By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

INSERT... SELECT.. with ON DUPLICATE KEY... Is this possible?

P: n/a
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.

-Nick
Jul 20 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Nick wrote:
Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick


Okay, then can you be more specific regarding not being able to get the
feature to work. It looks like you are trying to set field2 to the
value 1 on a failed insert. I'll make a guess you meant to increment
the value of field2, in which case you should do this:

INSERT INTO table1
SELECT field1, field2
FROM table2
ON DUPLICATE KEY UPDATE field2 = field2 + 1

Refer to http://dev.mysql.com/doc/mysql/en/INSERT.html for more information.

Regards,
Bill K.
Jul 20 '05 #2

P: n/a
Nick wrote:
Is it possible to use both INSERT... SELECT... with ON DUPLICATE KEY?
I cant get it to work.


I haven't used it, but I noticed the docs say that this feature is new
as of MySQL 4.1.0. Are you using that version of MySQL or later?

Regards,
Bill K.
Jul 20 '05 #3

P: n/a
Sorry for the typo Bill. I tried using the above example you gave me
which returns the error message:

Syntax error or access violation, message from server:
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use.

The MySQL documentation link doesnt show the option for ON DUPLICATE
KEY for INSERT... SELECT...

Were you able to get it to work? -Nick
Jul 20 '05 #4

P: n/a
Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick
Jul 20 '05 #5

P: n/a
Nick wrote:
The MySQL documentation link doesnt show the option for ON DUPLICATE
KEY for INSERT... SELECT...
On page http://dev.mysql.com/doc/mysql/en/INSERT.html:

"If you use an INSERT ... VALUES statement with multiple value lists or
INSERT ... SELECT, the statement returns an information string in this
format:
...."
Were you able to get it to work? -Nick


No, I'm using MySQL 4.0.20. So I can't offer any advice from my own
experience. I agree that it's a useful feature, but it's not standard
SQL. Since it's not working, it might be easier to code around it the
old-fashioned way. That is, fetch the dataset you want to insert, and
filter out entries that already exist in the destination table.

Regards,
Bill K.
Jul 20 '05 #6

P: n/a
Nick wrote:
Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick


Okay, then can you be more specific regarding not being able to get the
feature to work. It looks like you are trying to set field2 to the
value 1 on a failed insert. I'll make a guess you meant to increment
the value of field2, in which case you should do this:

INSERT INTO table1
SELECT field1, field2
FROM table2
ON DUPLICATE KEY UPDATE field2 = field2 + 1

Refer to http://dev.mysql.com/doc/mysql/en/INSERT.html for more information.

Regards,
Bill K.
Jul 20 '05 #7

P: n/a
Sorry for the typo Bill. I tried using the above example you gave me
which returns the error message:

Syntax error or access violation, message from server:
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use.

The MySQL documentation link doesnt show the option for ON DUPLICATE
KEY for INSERT... SELECT...

Were you able to get it to work? -Nick
Jul 20 '05 #8

P: n/a
Nick wrote:
The MySQL documentation link doesnt show the option for ON DUPLICATE
KEY for INSERT... SELECT...
On page http://dev.mysql.com/doc/mysql/en/INSERT.html:

"If you use an INSERT ... VALUES statement with multiple value lists or
INSERT ... SELECT, the statement returns an information string in this
format:
...."
Were you able to get it to work? -Nick


No, I'm using MySQL 4.0.20. So I can't offer any advice from my own
experience. I agree that it's a useful feature, but it's not standard
SQL. Since it's not working, it might be easier to code around it the
old-fashioned way. That is, fetch the dataset you want to insert, and
filter out entries that already exist in the destination table.

Regards,
Bill K.
Jul 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.