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

[Nick]

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

[Bill Karwin]

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.

[Bill Karwin]

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.

[Nick]

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

[Nick]

Yes, Im using 5.0 for Mac OSX. That feature would reaaallly help me out. -Nick

[Bill Karwin]

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.

[Bill Karwin]

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.

[Nick]

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

[Bill Karwin]

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.