MYSQL :: SELECT (INSERT...) Query

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

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.

How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?

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.

INSERT Into A, SELECT From B - But With Changes
INSERT INTO A (email,newsletter,date_added)

SELECT email,newsletter,date_added

FROM B Where foo=bar

Instead of inserting the data from B.date_added, I need to insert now() into A.date_added ...

INSERT .... SELECT
I have some trouble since i've upgradet 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.

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);

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?

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?

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)?

Insert Select
I am trying to insert data into a table from another table. The problem comes in with the unix timestamp column. It always converts the the date to 0000-00-00 but if you run the select query by itself it works just fine(Gives the correct date). Could someone see what I am doing wrong. Here is my SQL statement: Code:

INSERT .... SELECT
I have some trouble since i've upgradet 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.

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

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...

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).

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.

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).

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?

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.

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.

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.

Insert Into Select
i have a table named tmptxns where banks and respcode are its fields. My goal is to segregate or get all the approved data and rejected data from respcode field of tmptxns and store it in two separate table.
so i will have tmpApp and tmpRej tables..note that all its data came from tmptxns..
how will i execute this in insert into select?

SELECT And INSERT
I have an interesting problem. Interesting as in "headache", mind you. Let me sketch the depressing premises:
* Must cater for MySQL 3 AND 5 (well, I can test it in 3, but must implement it in 5)
* Uses an obscure and very limited interface
* Every query executed is one too many, really

Anyway, what I need is ONE query that inserts a new record and returns the ID of that record. I can only readout standard SELECT results. I don't trust the normal secondary INSERT_ID() call at all in this case.

I was thinking about stored procedures, but they do not seem to exist in MySQL 3, and I don't know how they work. I don't even know how procedures work at all, I am afraid.

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.

INSERT SELECT
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).

SELECT And INSERT
I'm a little stuck with a SQL query, actually I don't know how to do it at all and if its possible.
Basically I got 2 tables user_table and group_user
i need to extract userid and group_id from the user_table and insert both into group_user.
How do I do this? I know it can be done using php but I just wanted to do it via the database.

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?

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.

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.

Insert Into Select + Delete?
Is there a way to copy some data from 1 table to another in a insert into...select statement AND remove the affected data from the original table?

ie combining these 2 statements:
CODEinsert into superCat_subCat (sCatId, subCatId) select from cat_subCat where catId = 327

delete from cat_subCat where catId = 327

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:

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?

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

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

Insert And Select Commands.
I am using Mysql 4.1 on a shared hosting server on godaddy. I am having trouble with trying to construct a SQL command that does the following.

Replace values in the field 'category' in table "approved" when values in the field "description" are LIKE %blue%. I can do the commands seperately but cannot seem to combine them together.

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'

How To Select Something And Then Insert Into Another Table
I have two table, tesing and retail. They all have same column (field): id, item, price. how do I write a syntax to select id, item and price and then insert into retail table.

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)?

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.).

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

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)

Data Truncation With SELECT... INSERT
I'm trying to copy a whole load of rows from one table to another.
The tables should be identical since the same SQL was used to create them both.

Here's the SELECT...INSERT I'm using

insert into jobarchive (
id,consultantId,branchId,reference,postDate,location,type,timeType,title,description,status,salaryFrom,salaryPre,salaryTo,salaryPeriod,salaryOptions,views,archive,lastUpdate,created
)
SELECT
id,consultantId,branchId,reference,postDate,location,type,timeType,title,description,status,salaryFrom,salaryPre,salaryTo,salaryPeriod,salaryOptions,views,archive,lastUpdate,created
FROM jobs WHERE archive="yes"

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.

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?

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).

CREATE TABLE, INSERT INTO With SELECT In Parentheses
Assuming a legal SELECT statement, this works fine:

CREATE TABLE Foo SELECT ...

but this does not:

CREATE TABLE Foo (SELECT ...)

This is a problem for me as I'd like to use the output of a
SELECT...UNION...ORDER BY statement as input to CREATE TABLE.

Same holds for INSERT INTO.

Is this a bug?

Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine)

Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do)

insert joomla2.jos_magazine_articles
(joomla2.jos_magazine_articles.name,
joomla2.jos_magazine_articles.article_id,
joomla2.jos_magazine_articles.category_id,
joomla2.jos_magazine_articles.catid)

select
joomla2.jos_content.title,
joomla2.jos_content.id,
joomla2.jos_content.catid,
maxdev.jos_content.catid

SELECT (INSERT...) Query

Is there any way I can do an insert and select the auto_incremented id in one SQL command?