Is This INSERT With SELECT As Efficient As Possible?
I'm writing a pretty complex web app and will be repeating many times over a query very similar to below and need to know if there is a more efficient way to do it. If anyone has input, I'd be happy to hear:
INSERT INTO table (somecolumn) VALUES ((SELECT id FROM other_table WHERE foo = 'bar'))
View Complete Forum Thread with Replies
Related Forum Messages:
Efficient SELECT From Multiple Tables With Same Formatting?
Let's say I have 2 tables with the exact same formatting (field/column names, etc) -- the only difference is the name of the tables.. one is called "table1" the other is called "table2" If I wanted to query both of the together is this legal and good coding practice? "SELECT id, headline, permalink, body FROM table2007_11, table2007_12 WHERE id=23" understandly I get this error: Column 'id' in field list is ambiguous" is there a better way to do this than printing out each table name for each field individually? (ie. id. table2007_11, id. table2007_12, etc)?
View Replies !
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: ERROR 1064 (42000): 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 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
INSERT SELECT
I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT) Thanks again //Lars Rasmussen -----Oprindelig meddelelse----- Fra: Jay Blanchard [mailto:jay.blanchard@niicommunications.com] Sendt: 13. august 2003 19:59 Til: Lars Rasmussen; mysql@lists.mysql.com Emne: RE: INSERT .... SELECT [snip] I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 [/snip] * does not return a specific number of columns, the work around is to specify the columns explicitly INSERT INTO nye_opskrifter SELECT foo, bar FROM opskrifter where id in($numbers).
View Replies !
INSERT .... SELECT
I need to insert a way that mysql doese'nt complain when i copy some records that have the same id (or that it just gives it an id according to the AUTO_INCREMENT).
View Replies !
INSERT... SELECT
Im trying to insert a bunch of rows into a table. If the row already exists id like to update the row 'counter'. For example... INSERT INTO table1 SELECT field1, field2 FROM table2 ON DUPLICATE KEY UPDATE field2 = 1 Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY? I cant get it to work.
View Replies !
Insert Into Xyz Select * From Abc
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of "abc". Is there any simple way to inject all the rows of "abc" into "xyz"? Tried "insert into xyz select * from abc" but got a complaint of "column count doesn't match value count at row 1", which of course is true. Any way to tell SQL to simply set defaults for the missing columns?
View Replies !
INSERT And SELECT
I'm wondering if it is possible to run an INSERT...SELECT query where I am grabbing data from Database1 with the SELECT query and inserting this into tables in Database2 with the INSERT.I saw one of the comments over at: http://dev.mysql.com/doc/refman/5.0...ert-select.html (third or fourth comment down) suggested that this was possible, al-be-it this is in the manual for version 5 of MySQL and I am using 4.1.11
View Replies !
INSERT/SELECT Row
I'm trying to duplicate multiple rows from table_parent into table_child. However, I would like to set the value of column1 normaly. (i.e. insert a static value not retrieved from table_parent) Both column1 and column2 are primary keys in both tables. Column1 needs a different value in table_child from what it has in table_parent. The SQL for a direct duplication would be: PHP Code: mysql_query("INSERT INTO table_child(column1, column2) SELECT column1, column2 FROM table_parent WHERE column1 = $var_column1", $connection); How do I change the values being inputted from table_parent.column1, table_parent.column2 to $var_phpVariable, table_parent.column2? I can do this simply enough with a loop but would rather not spend up to 30+ queries when it could be done in one query.
View Replies !
Insert Using Select
Can anyone let me know if it possible to formulate an insert query that that can use both select and values, so that I can get certain data from the another table and have fixed values for some of the fields in the insert query? I guess one way would be to do an insert for select statement, then an update for fixed values.
View Replies !
INSERT With SELECT
I've been working on a stored procedure which takes rows from one table and inserts them into a temporary created memory table, but I've stumbled accross a small roadblock (I hope at least)... Here's the statement I'm trying to exectute INSERT INTO temptable SELECT * FROM template WHERE type_name like '%jack%' ORDER BY RAND() LIMIT 1; if I take out the ORDER BY RAND() it works, but natrually it always selects the first record, is it possible to have the ORDER BY RAND() in that select, or would I need to do it another way...
View Replies !
Insert Using A Select
Can seomeone tell me what I'm doing wrong? Here's a simplified version of what I'm doing: CREATE TABLE `test1` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; CREATE TABLE `test2` ( `val1` VARCHAR( 5 ) NOT NULL , `val2` VARCHAR( 5 ) NOT NULL, `val3` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM ; insert into test2 (val1, val2, val3) values ("some", "test", "values"); ******** Then I try to do this, am I doing something wrong or is this not possible? insert into test1 (test1.val1, test1.val2) values (select test2.val1, test2.val2 from test2 where 1);
View Replies !
Insert Into Select
I've search and search and I haven't found the answer. I am new to MySQL but not new to SQL. I teach an SQL class. This term, we choose to use MySQL. We are trying to execute this: Insert into temp_table select zip from customer temp_table is being created on the fly. We get the following error: Error 1146 (42S02) ...table 'temp_table' doesn't exist.
View Replies !
Insert And Immediate Select
I add a bunch of records using ExecuteNonQuery() in C# (OdbcCommand). Immediately after the INSERT, I do a SELECT (with a different connection) on the same data. And once in a while (not often, but still...), the records just inserted is not included. If I try again, it works. Each time, "rows affected" return a value greater than 0, so the data should be there somewhere... Is there a way to make sure that records are "firmly" added to the database before I go ahead with the subsequent SELECT (preferably without using transactions)?
View Replies !
INSERT ... SELECT ... AND
i try to do a subquery on INSERT ... SELECT i need the id of two entrys from one table and want to insert them into a nother table. also i want to insert a value to the same table. this syntax doesnt work... INSERT INTO a (ID1, ID2, C) SELECT lemma.id FROM lemma WHERE lemma.lemma="my" AND SELECT lemma.id FROM lemma WHERE lemma.lemma="question" AND VALUE (4); how can i do this?
View Replies !
INSERT INTO SELECT
I am new to mysql and would appreciate someone helping me out with this converting this statement from postgresql to mysql. INSERT INTO ossurjoinquestions SELECT *, RANDOM() as random_sort, trunc(1144355460, 0) as instanceid FROM ossurcommonquestions WHERE tid = '3'; So far I have INSERT INTO ossurjoinquestions( tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path, random_sort, instanceid ) SELECT tid, question, ans1, ans2, ans3, ans4, ans5, corans, image_path FROM ossurcommonquestions WHERE tid = '3' What I am not sure how to do is add RAND() as random_sort or trunc($var,0) as instanceid.
View Replies !
Insert Then Select
if I have table with a field named myCounter and i auto increments when I do an insert, what is the best way to get that value. This is what I am doing now. myTime = Now Insert into myTable (Value1, Value2) values (myTime, "myValue") 'then I do this right after to get myCounter Select myCounter from myTable where Value1 = myTime and Value2 = myValue
View Replies !
Insert Into ... Select
I am looking at creating a simple versioing system for a small CMS I have written. An obvious way to do this would seem to me to do a INSERT INTO .. SELECT whenever the page is amended, but before the amendments are stored. ie copy the data first. But although the structure of the table is virtually the same, I need to add columns to it for the actual versioin number eg. Table A has 'id, title, content, editdate' Table B has 'uniqueid, version,id, title, content, editdate' Though the position of 'version' can actually be anywhere.(uniqueid may, or may not, be required) Can I achieve this in one? and if so how?
View Replies !
Insert From Select
INSERT INTO thistable (SELECT * FROM thistable WHERE id=Ƈ') The problem is, there is a unique key ID, thus that statement generates an error duplicate key. I know that I can change the sql by listing all the fields, but is there any other way? because if I list all the fields, firstly there are many fields to be type in and secondly if I happened to add/edit some fields later and I forget to change this sql, then it wil produce wrong result.
View Replies !
INSERT SELECT Not Working
I have some trouble since i've upgraded from 3.23.49 to 4.0.13. I used this command: INSERT INTO nye_opskrifter SELECT * FROM opskrifter where id in($numbers) But now it gives this error: Column count doesn't match value count at row 1 I tried IGNORE, but it's just not working. I hope anyone of you got a workaround for this problem, i did'nt find any in the manual.
View Replies !
INSERT SELECT In One Query
I keep getting an error with this query: Insert into new_voter (salutation, firstname, lastname, electoral_district, poll_number, birthdate, gender, occupation, party_affiliation, ph_home, email) values select (txtSalutation, firstname, lastname, txtElectDistrict, txtVotingArea, dtBirth, txtGender, txtOccupation, nPartyId, txtPhoneDay, email) from person Can someone spot the error (presumably with the syntax).
View Replies !
How To Insert If A Value Does Not Exist And Select If It Does?
Basically, I am doing a users table with an ID and a name for now. I want to create a stored function that returns the ID of a user and creates the user if it does not already exist. The way I do it for now is: Select the user record by name. If we had one, return its ID. If not, insert a new user record and return its ID. But I am worried about concurrency. What if another thread runs the same function while this one is between steps 2 and 3? TWO user records would be created for the same user. That would be bad. I added a UNIQUE index to the username, but this means that the stored function will just error in a case like this, instead of returning the already existing user. This is also undesirable.
View Replies !
INSERT Values From SELECT
This works: INSERT INTO content (group_id) SELECT max(id)+1 FROM content; But how do I enter into more than one field? This doesn't work. INSERT INTO content (group_id, content_id) (SELECT max(id)+1 FROM content), 55; OR INSERT INTO content (group_id, content_id) VALUES ((SELECT max(id)+1 FROM content), 11); You can't specify target table 'content' for update in FROM clause
View Replies !
Insert...select...error
I am getting this error msg: Subquery returns more than 1 row ("INSERT INTO customerbook (booking_ID,room_Type) values((select booking_ID from bookings where booking_ID = bookings.booking_ID),'$a')") ID is reference from table1... I m tryin to get this output: ID type 1 single 1 ensuite any idea?
View Replies !
Select From Table Before Insert
I am trying to create a trigger that will select the value for an integer from the table on which the insert is occuring, increment it by one, and insert the value into the new row on the same table. Basically, we have a user referral id. I want to grab the rank of the referral user, increment it, and insert it into the rank of the new user. i cannot figure out how to do this for the life of me. Code:
View Replies !
SELECT, If Not Exist, INSERT
1) SELECT ID WHERE pageurl='...' LIMIT 1; 2) if(ID is empty): INSERT INTO ... (pageurl) VALUES ('...'); ID=mysql_last_insert_id(); Is there a more efficient way of doing this?
View Replies !
Insert With Both Values And Select
I need to insert multiple values in a table I use an insertstatement like this one insert into table(name, jobID) select 'Jon', id from jobs where name = 'programmer'; However, if I have a value before the condition like insert into table (jobID, name) select id from jobs where name='programmer', 'jon2'; it does not work and I need such a statement to work cuz I get different data with a lot of fields and I do not know in advance which will be fields with have direct values and hich will ghave to get data from other tables
View Replies !
Concurrent Select And Insert
we have a system that encompasses hundreds of nodes. We use a MySQL db to archive messages prouduced by different applications sent via an in-house transport mechanism that follows the producer/server/consumer scheme. As you can imagine the message transmission rate can be fairly large. A PHP based tool can be used to browse the messages stored on the database. An application runs in the MySQL server to get the messages and put them on the db. The problem is that when the messages are browsed with th PHP-based tool, the mysqld SELECT thread blocks the mysqld INSERT threads created by the 'Receiver' application. This, cannot send an acknowledge to the transport mechanism and the conection is broke after a 1 sec. timeout, in other words it does no longer receive messages. My question is then if there is a way of forcing the INSERT opeartion even when a lenghty SELECT is taking place. I'm using MySQL 3.24 and have tried INSERT HIGH_PRIORITY and INSERT DELAYED with no success.
View Replies !
Insert Into Table A Select
I am testing out the innodb table types and I think that this statement should be able to work according to the transaction model for Innodb tables. create table a (c1 char(10) type=innodb; insert into a values ('aaa'); commit; insert into a select * from a; I get the following error: ERROR 1066: Not unique table/alias: 'a'
View Replies !
Select And Insert In One Process?
hi there, sorry if this sounds like a newb question - it is. please be gentle i have created a registration form using php which allows users to enter their info into a table called "retailer". one field entered is a postcode. there is another table, "domicile" which contains every postcode in the country as well as the mail centres that service each post code. there are say 10 postcodes per mail centre. during the registration process, i need a function that will use the postcode entered to look up the name of the relevant mail centre (from "domicile") and then store that name in the table "retailer" under the locale column. i can get the result i need with the query SELECT locale FROM domicile WHERE pcode=5555 LIMIT 1 where 5555 is the postcode. I am unsure though, as to how to properly implement this into my php code. PHP $physical_pcode = $_POST['physical_pcode']; $locale = @mysql_query('SELECT locale FROM domicile WHERE pcode=$physical_pcode LIMIT 1'); $sql = "INSERT INTO retailer SET physical_pcode='$physical_pcode', locale='$locale'"; but the field remains empty. Maybe I need to break this up into separate parts?
View Replies !
Combining An Insert And Select?
Is it possible to combine a select and insert statement into one statement? I would like to combine these two statements into a single database call. SELECT tx.account_balance as last_balance FROM transactions AS tx INNER JOIN ( SELECT MAX( txn_id ) AS last_tran FROM transactions WHERE user_id =63 ) AS m ON tx.txn_id = m.last_tran INSERT INTO transactions (user, value) VALUES ($user, last_balance+$amount )
View Replies !
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
INSERT INTO ... SELECT (...) Performance Questions
Using version: 4.0.18-Max-log We're getting some fairly poor performance when doing an INSERT INTO temp_table ... SELECT (...). The SELECT is grabbing 2K row chunks from its table, so the problem is not that the query results are so monstrous that it's just taking that long to gather them all. The problem appears to be one of IO. When an INSERT...SELECT is run, the SELECT part of the query is run first and those results saved in a temporary table. Then the contents of that temporary table are inserted into its final destination, which in our case *also* happens to be a temporary table. All of this table reading and writing is causing things to drag. Unfortunately I don't have our MySQL variables handy or our hardware specs. But I can get them in fairly short order either is needed. I do know that our hardware is < 6 months old and has a few gig of RAM. The discs, if I recall, are a RAID array but off the top of my head I don't know which flavor of RAID. Our questions: 1) Why does MySQL write to a separate temp table when the destination table is already a temp? 2) Is there some way to make the results of the SELECT write directly to the destination table (be it temporary or not)? 3) Are there any other suggestions for resolving this performance problem? Maybe there's a variable which at least will force the SELECT to write to a TEMPORARY HEAP table instead and avoid the extra disc IO? --V. M. Brasseur Software/Database Engineer iPost http://www.ipost.com
View Replies !
Insert ... Select Special Character
I have a project where it is necessary to copy rows (and change a few pieces of information); however some of the rows may contain special characters such as an apostrophie or quotes. I am using the INSERT INTO ... SELECT statement to accomplish the copy fairly quickly... however for those rows with special characters, I get a Syntax error. Is there an easy way around this, or do I need to abandon the INSERT INTO ... SELECT shortcut? ie: "INSERT INTO `table` (field1,field2,field3) SELECT field1,field2,field3 FROM `table` WHERE field0=1" returns a syntax error if any of the fields contain a special chracter.
View Replies !
Select IMMEDIATLY After An Insert Statement
a program in php, javascript, mysql and ajax (xajax library). A button on my screen inserts one (1) row in a table. Immediatly aftersward, I want to refresh the table on my screen, so I do a select: Expand|Select|Wrap|Line Numbers
View Replies !
Insert With Nesting Select Using Two Connections
basically, I just need to use the insert..select format, as in... insert into mytable ( val1, val2 ) select val1, val2 from myOtherTable except that I need to read the select query from one database on one server, and insert into another database on a different server. I was thinking I could somehow specify the login/server information for each nested query so It would read/write data to the correct place.
View Replies !
Insert Into Select - Connection Lost
my problem - want to write a query - find new records in one table and insert in an ohter to generate new keys... See below the statement - but wenn I execute the select part it works fine - as soon as I add the insert into for the select i loose the connection to the mysql server (Error 2013)
View Replies !
Insert With Nested Select Problem
I want to implement a query similar to this in mysql: insert into table1 (field1) (select t2.field2 from table2 t2 where t2.field3="string" and t2.field4=table1.field4) the reference to table1 from the inner query (table1.field4) does not work. Is there a way to achieve this?
View Replies !
Insert Select Duplicate Last_insert_id
-- MySQL v5.0.60 create table t (f int unsigned primary key auto_increment); insert into t values(42); insert into t values(last_insert_id(42)) on duplicate key update f = f; select last_insert_id(); -- -> 42 (ok) insert into t select last_insert_id(42) on duplicate key update f = f; select last_insert_id(); -- -> 0 (why?!)
View Replies !
Insert From Select Joining Values For One Value...
I have a table called users ------------ |Id | State| ------------ | 1 | CA | ------------ | 2 | OR | ------------ I have a table called invite ------------------------------ | InviteId | Active | UserId | ------------------------------ I want to select the values from the users put them together with a unique count and insert in to invite. INSERT INTO invite (InviteId, Active, UserId) the InviteId is based on user Id and state and amount so it should look something like this: InviteId = UserId+State+'N'+count ----------------------- | CA1N1 | 0 | 1 | ----------------------- | CA1N2 | 0 | 1 | ----------------------- | CA1N3 | 0 | 1 | ----------------------- | CA1N...60 | 0 | 1 | -----------------------
View Replies !
Insert And Select Row With An Auto Incrimented Value
I have a row of data being inserted into my database. When a row is entered, SQL gives it a 'movie_id' which I is an auto-incrimenting value. As soon as a row is entered, I would like to be able to call the movie_id that the row was given. I presume that I will have to use anothe SELECT command but how do I identify the last entered row (all other values could have duplicate entries, movie_id is the only unique value).
View Replies !
Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't. There is a table, s01_Baskets, that tracks the users basket throughout my store. There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id. What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id: SELECT s01_StoreKeys.maxvalue FROM s01_StoreKeys WHERE s01_StoreKeys.type = 'Baskets' Then I can "create" a basket by inserting a row: INSERT INTO s01_Baskets ( s01_Baskets.session_id , s01_Baskets.cust_id , s01_Baskets.basket_id , s01_Baskets.order_id , s01_Baskets.order_proc , s01_Baskets.last_update , s01_Baskets.ship_id ) VALUES ( '".session_id()."' , Ɔ' , Ɔ' , Ɔ' , Ɔ' , '".microtime()."' , Ɔ' )"; Then I have to update that field for next key for the next basket. UPDATE s01_StoreKeys SET s01_StoreKeys.maxvalue = '".$basket_id+1."' WHERE s01_StoreKeys.type = 'Baskets' UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....) Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?
View Replies !
Select Data From One Table Insert To Other
Can anyone give me some hints about a query. A query which takes a value from form field insert it to 1st table, then select other table within that query to select name,email from 2nd table and insert it to the 1st table from 2nd table,but condition is that in where clause it must check that dcode entered in the form value must match with 2nd table dcode(dcode is field in table.).
View Replies !
|
TRACKER
