Search Results

Search found 8603 results on 345 pages for 'altering tables'.

Page 261/345 | < Previous Page | 257 258 259 260 261 262 263 264 265 266 267 268  | Next Page >

• Database Optimization techniques for amateurs.

  - by Zombies

  Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being too vague, let's say we are using a maintstream DB such as MySQL or Oracle, and that the DB will contain 500,000-1m or so records across ~10 tables, some with foreign key contraints, all using the most typical storage engines (eg: InnoDB for MySQL). And of course, the basics such as PKs are defined as well as FK contraints.

  Read the article

• Need help with some complex SQL query

  - by Psyche

  Hello guys, I need some help with a complex SQL query. Here's my setup: there are two tables, one with authors, and another with books. The books table contains a field named "author" which holds author's id. If a book has more than one author, then the "author" field will contain all the authors ids separated by ";" (something like 2;34;234). On the website I have to list all the books written by an author. If there's only one author, its simple, but how can I get those books where the author I'm interested in is the second or third author? Many thanks.

  Read the article

• Microsoft Access DB Connection

  - by sikas

  I have a Microsoft Access DB (2003) that I want to connect to it using C# .. The problem I'm facing is I don't have Access installed within the office package .. So I was wondering if it is possible to connect to it as a database to retrieve and update the tables .. Thanks. UPDATE I have received the error below: Error Detected: System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapp er) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInf o, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnection PoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory conne ctionFactory) at System.Data.OleDb.OleDbConnection.Open() at SampleNamespace.SampleClass.Main()

  Read the article

• Return multiple results using dynamic sql (postgresql 8.2)

  - by precose

  I want to loop through schemas and get a result set that looks like this: Count 5 834 345 34 984 However, I can't get it to return anything using dynamic sql...I've tried everything but 8.2 is being a real pain. Here is my function: CREATE OR REPLACE FUNCTION dwh.adam_test4() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE myschema text; rec RECORD; BEGIN FOR myschema IN select distinct c.table_schema, d.p_id from information_schema.tables t inner join information_schema.columns c on (t.table_schema = t.table_schema and t.table_name = c.table_name) join dwh.sgmt_clients d on c.table_schema = lower(d.userid) where c.table_name = 'fact_members' and c.column_name = 'debit_card' and t.table_schema NOT LIKE 'pg_%' and t.table_schema NOT IN ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd') order by table_schema LOOP EXECUTE 'select count(ucic) from '|| myschema || '.' ||'fact_members where debit_card = ''yes''' into rec; RETURN rec; END LOOP; END $function$

  Read the article

• MySQL LEFT JOIN issue with three WHERE statements

  - by jhat

  I am building a note taking app for myself with tag filtering functions, but am having an issue when trying to grab notes with the tags. The tag filter needs to use AND not IN, because it will help better narrow down what I am looking for. My tables are configured like this: + notes note_id | note_title | note_uid + tags tag_id | tag_title + notes_tags nt_id | nt_note_id | nt_tag_id The notes_tags table keeps track of all notes' tags. I am not worried about returning any information about tags, so here is an example LEFT JOIN I am using currently to only get notes with only 1 tag. SELECT * FROM notes_tags LEFT JOIN notes ON note_id = nt_note_id WHERE note_uid IN ( 1 ) AND nt_tag_id = 10 This query runs perfect, it grabs all of the notes with that single tag. However, I am having issues "pinpointing" my notes using a query like this: SELECT * FROM notes_tags LEFT JOIN notes ON note_id = nt_note_id WHERE note_uid IN ( 1 ) AND nt_tag_id = 10 AND nt_tag_id = 11 What am I doing wrong with the syntax?

  Read the article

• Why is Zend Framework (Zend_Db_table) rejecting this SQL Query?

  - by Michael T. Smith

  I'm working on a simple JOIN of two tables (urls and companies). I am using this query call: print $this->_db->select()->from(array('u' => 'urls'), array('id', 'url', 'company_id')) ->join(array('c' => 'companies'), 'u.company_id = c.id'); which is out putting this query: SELECT `u`.`id`, `u`.`url`, `u`.`company_id`, `c`.* FROM `urls` AS `u` INNER JOIN `companies` AS `c` ON u.company_id = c.id Now, I'd prefer the c.* to not actually appear, but either way it doesn't matter. ZF dies with this error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" but I can run that query perfectly fine in my MySQL CLI. Any ideas how to fix up this query?

  Read the article

• Beginner SQL question(s)

  - by unit

  I am two months in to an intro sql course, it's late at night, and I am drawing a blank. I have two tables, one customers, and one orders. I have to increase any customers credit limit by twenty five percent for all customers who have made two or more orders in which each order is more than the amount of 250.00. I get how to UPDATE CreditLimit * 1.25 and Cust with an order 250, but how the hell do I get it to check if they have made two orders over 250? Second question, we are just starting to take subqueries, and I am having a difficult time getting it into my skull. Another question posed by the prof of our class is to increase the credit limit of a customer who has an order that exceeds their credit limit. (Credit limit is on a customers table, order and amount are on an orders table). I then take that customer and UPDATE his CreditLimit +1000. Thanks for any help.

  Read the article

• copy an identity column into another table

  - by slake

  I have 2 tables that are related,both have identity columns for primary keys and i am using a vb form to insert data into them,My problem is that i cannot get the child table to get the primary key of the parent table and use this as its foreign key in my database. the data is inserted fine though no foreign key constraint is made.I am wondering if a trigger will do it and if so how. All my inserting of data is done in vb. The user wont insert any keys. all these are identity columns that are auto generated. If a trigger is my way out please illustrate with an example. If there is another way i can do this in VB itself then please advise and an example will be greatly appreciated Thanks in advance

  Read the article

• Running sql scripts against an attached database?

  - by Will

  I've got an MDF attached to an instance of Sql Server 2008 Express, and I need to run some sql scripts against it to generate tables, indexes, etc. But I can't figure out how to get this to work. If I load the scripts in Visual Studio, it only allows me to connect to the server and run it against a database. I can't choose a different provider (Microsoft Sql Server Database File), so I can't select my MDF. This leaves me the only option of running the script as individual queries, but that won't work as it appears it doesn't support TSQL CREATE statements. How can I run my sql script against an attached database?

  Read the article

• Help me construct this Linq statement

  - by Geoffrey

  There should be a simple Linq query for what I'm trying to accomplish, but I'm producing some ugly code. I have two tables, one of issues and another of issue status. There is a one-to-many relationship between issue and issue status. When an issue is created an IssueStatus is also created with the status field set to "Open" when it is closed, another IssueStatus is created with the status field set to "Closed" ... but issues can be re-opened. It seems like I should be able to write something like this: public static List<Issue> FindOpenIssues(this IList<Issue> issues) { return ( from issue in issues from issueStatus in issue.issueStatus.OrderBy(x=>x.CreatedOn).Single() where issueStatus.Status == "Open" select issue ).ToList(); } This obviously fails, but there must be a clean way to do this? Thanks!

  Read the article

• PHP, MySQL - would results-array shuffle be quicker than "select... order by rand()"?

  - by sombe

  I've been reading a lot about the disadvantages of using "order by rand" so I don't need update on that. I was thinking, since I only need a limited amount of rows retrieved from the db to be randomized, maybe I should do: $r = $db->query("select * from table limit 500"); for($i;$i<500;$i++) $arr[$i]=mysqli_fetch_assoc($r); shuffle($arr); (i know this only randomizes the 500 first rows, be it). would that be faster than $r = $db->("select * from table order by rand() limit 500"); let me just mention, say the db tables were packed with more than...10,000 rows. why don't you do it yourself?!? - well, i have, but i'm looking for your experienced opinion. thanks!

  Read the article

• Problem using OLEDBCOMMANDBUILDER.

  - by Lullly

  So, here it goes: I need to copy data from table in access database, in another table from another access database. Column names from tables are the same, except the fact that the FROM table has 5 columns, the TO table has 6. here is my code: dsFrom.Clear() dsTO.Clear() daFrom = Nothing daTO = Nothing conn_string1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="etc.mdb;" conn_string2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="database.mdb;" query1 = "Select * from nomenclator_produse" query2 = "Select * from nomenclator_produse" Conn1 = New OleDbConnection(conn_string1) conn2 = New OleDbConnection(conn_string2) Conn1.Open() conn2.Open() daFrom = New OleDbDataAdapter(query1, Conn1) daTO = New OleDbDataAdapter(query2, conn2) daFrom.AcceptChangesDuringFill = False dsFrom.HasChanges() daFrom.Fill(dsFrom, "nomenclator_Produse") dsFrom.HasChanges() Dim cb = New OleDbCommandBuilder(daFrom) dsTO = dsFrom.Copy daTO.UpdateCommand = cb.GetUpdateCommand daTO.InsertCommand = cb.GetInsertCommand daTO.Update(dsTO, "nomenclator_produse") Because the FROM table has 5 rows and the other has 6, i'm trying to use the InsertCommand generated by the DataAdapter of the first table. It works, only that it inserts the data from the FROMTABLE in the same FROMTABLE, instead of TOTABLE. :| please help me :(

  Read the article

• iOS Development: Can I store an array of integers in a Core Data object without creating a new table to represent the array?

  - by BeachRunnerJoe

  Hello. I'm using Core Data and I'm trying to figure out the simplest way to store an array of integers in one of my Core Data entities. Currently, my entities contain various arrays of objects that are more complex than a single number, so it makes sense to represent those arrays as tables in my DB and attach them using relationships. If I want to store a simple array of integers, do I need to create a new table with a single column and attach it using a one-to-many relationship? Or is there a more simple way? Thanks in advance for your wisdom!

  Read the article

• TSQL - create a stored proc inside a transaction statement

  - by Chris L

  I have a sql script that is set to roll to production. I've wrapped the various projects into separate transactions. In each of the transactions we created stored procedures. I'm getting error messages Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'procedure'. I created this example script to illustrate Begin Try Begin Transaction -- do a bunch of add/alter tables here -- do a bunch of data manipulation/population here -- create a stored proc create procedure dbo.test as begin select * from some_table end Commit End Try Begin Catch Rollback Declare @Msg nvarchar(max) Select @Msg=Error_Message(); RaisError('Error Occured: %s', 20, 101,@Msg) With Log; End Catch The error seems to imply that I can't create stored procs inside of transaction, but I'm not finding any docs that say otherwise(maybe google isn't being freindly today).

  Read the article

• jQuery - add onclick to TR

  - by Guus

  I use Richfaces and one of the rich:dataTable generates this html: <form id="scheduleForm"> <table id="scheduleForm:rowList"> <tr> <td></td> </tr> </table> </form> I want to act on a click on a TR for this table. How can I do this? The following also works for other tables on the page and I get the alert() for the other rows as well, and I don't want that. jQuery('#scheduleForm:rowList tr').click(function(event) { alert(1); });

  Read the article

• How to retrieve base class only (entity framework)?

  - by Juvaly

  Hi All, I've been scratching my head here for a while now... I have a Consumer class and a BillableConsumer class that inherits Consumer. They are both a part of the Consumers set. The problem is that this following query: Consumer consumer = (from c in _ctx.Consumers where c.ID = id select c).First(); returns a BillableConsumer instance! Just the same as this query: BillableConsumer bconsumer = (from c in _ctx.Consumers.OfType<BillableConsumer>() where c.ID = id select c).First(); How can I return an instance of just the base class? (these are separate tables in the data store).

  Read the article

• MySQL - accessing a table sum and compare to another table?

  - by assignment_operator

  This is for a homework assignment. I just plain don't understand how to do it. The instructions for this particular question is: List the branch name for all branches that have at least one book that has at least 4 copies on hand. Where the tables in question are: Branch: BranchName | BranchId Henry Downtown | 1 16 Riverview | 2 Henry On The Hill | 3 Inventory: BookId | BranchId | OnHand 1 | 1 | 2 2 | 3 | 4 3 | 1 | 8 4 | 3 | 1 5 | 1 | 2 6 | 2 | 3 From what I understand, I can get the number of OnHand per branch name with: SELECT BranchName, SUM(OnHand) FROM Branch B, Inventory I WHERE B.BranchId = I.BranchId GROUP BY BranchName; but I don't get how I'd do the comparison between the sum of OnHand per branch and 4. Any help would be appreciated, guys!

  Read the article

• How to design a database schema for storing text in multiple languages?

  - by stach

  We have a PostgreSQL database. And we have several tables which need to keep certain data in several languages (the list of possible languages is thankfully system-wide defined). For example lets start with: create table blah (id serial, foo text, bar text); Now, let's make it multilingual. How about: create table blah (id serial, foo_en text, foo_de text, foo_jp text, bar_en text, bar_de text, bar_jp text); That would be good for full-text search in Postgres. Just add a tsvector column for each language. But is it optimal? Maybe we should use another table to keep the translations? Like: create table texts (id serial, colspec text, obj_id int, language text, data text); Maybe, just maybe, we should use something else - something out of the SQL world? Any help is appreciated.

  Read the article

• Fastest method for SQL Server inserts, updates, selects

  - by Ian

  I use SPs and this isn't an SP vs code-behind "Build your SQL command" question. I'm looking for a high-throughput method for a backend app that handles many small transactions. I use SQLDataReader for most of the returns since forward only works in most cases for me. I've seen it done many ways, and used most of them myself. Methods that define and accept the stored procedure parameters as parameters themselves and build using cmd.Parameters.Add (with or without specifying the DB value type and/or length) Assembling your SP params and their values into an array or hashtable, then passing to a more abstract method that parses the collection and then runs cmd.Parameters.Add Classes that represent tables, initializing the class upon need, setting the public properties that represent the table fields, and calling methods like Save, Load, etc I'm sure there are others I've seen but can't think of at the moment as well. I'm open to all suggestions.

  Read the article

• LINQ to Entites: Doing a count over one-to-many relationships

  - by chris

  I have a couple of tables where there are one to many relationships. Let's say I have a Country table, a State table with a FK to Country, and a City table with a FK to State. I'd like to be able to create a count of all Cities for a given country, and also a count of cities based on a filter - something like: foreach( var country in Model.Country ) { total = country.State.All().City.All().Count() ; filtered = country.State.All().City.Any(c=>c.field == value).Count(); } Obviously, this doesn't work - is there any way to do this?

  Read the article

• SQL Convert Nvarchar(255) to DateTime problem

  - by steven

  Hi, I'm using SQL server 2008. I have 2 Tables: Table 1 and Table 2. Table 1 has 1 column called: OldDate which is nvarchar(255), null Table 2 has 1 column called: NewDate which is datetime, not null Example data in Table 1: 26/07/03 NULL NULL 23/07/2003 7/26/2003 NULL 28/07/03 When i try CAST(OldDate as datetime) I get this error: Arithmetic overflow error converting expression to data type datetime. I need to insert OldDate into NewDate with no errors. I can't skip some rows. Anyhelp would be appreciated.

  Read the article

• Advice regarding Java frameworks [closed]

  - by Mixiul

  I am in the process of creating a website/phone application using JQuery Mobile and PhoneGap. I am hoping to add openID support so users can login in with pre-existing accounts. It will be necessary for me to have a database with a few tables to enable me to implement all the core functionality I desire. I haven't really had much experience with frameworks before, the closest thing I have done to this is create a basic website using php that connected to a MySql database stored on the machine that hosted the apache webserver. The interaction with the database won't be complex. I am required to use a java framework for the backend of my application. My question is which java framework is most suitable (flexible and straight forward to learn)? Any advice you guys can provide is greatly appreciated. Thanks

  Read the article

• Application Role and access second database

  - by lszk

  I have written a script to create an audit trails to my database in a second one db. So far I had no problems during tests on my dev machine from SQL Server Management Studio. Problems started to occurs when I first tried to test my triggers from my application by modyfing data in it. Using profiler I found out, that my audit trails db is not visible in sys.databases, so here lies the problem. The application using an Application Role, so as I found on MSDN, that's why I can't get access to other db on the server. I'm not a DBA. I have no experience with properly settings the security stuff, so please guide me, how can I set the setting for guest account (according to MSDN) to get access to this db? I need to have a record for this database in sys.databases and I need to be able to insert data in this database in all tables. No select, update or delete I need.

  Read the article

• Can I do this in only one query ?

  - by Paté

  Merry christmas everyone, I Know my way around SQL but I'm having a hard time figuring this one out. First here are my tables (examples) User id name friend from //userid to //userid If user 1 is friend with user 10 then you a row with 1,10. User 1 cannot be friend with user 10 if user 10 is not friend with user 1 so you have 1,10 10,1 It may look weird but I need those two rows per relations. Now I'm trying to make a query to select the users that have the most mutual friend with a given user. For example User 1 is friend with user 10,9 and 7 and user 8 is friend with 10,9 and 7 too ,I want to suggest user 1 to invite him (like facebook). I want to get like the 10 first people with the most mutual friend. The output would be like User,NumOfMutualFriends I dont know if that can be done in a single query ? Thanks in advance for any help.

  Read the article

• sports league database design

  - by John

  Hello, I'm developing a database to store statistics for a sports league. I'd like to show several tables: - league table that indicates the position of the team in the current and previous fixture - table that shows the position of a team in every fixture in the championship I have a matches table: Matches (IdMatch, IdTeam1, IdTeam2, GoalsTeam1, GoalsTeam2) Whith this table I can calculate the total points of every team based on the matches the team played. But every time I want to show the league table I have to calculate the points. Also I have a problem to calculate in which position classified a team in the last 10 fixtures cause I have to make 10 queries. To store the league table for every fixture in a database table is another approach, but every time I change a match already played I have to recalculate every fixture from there... Is there a better approach for this problem? Thanks

  Read the article

< Previous Page | 257 258 259 260 261 262 263 264 265 266 267 268  | Next Page >