Re: INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
Thanks Shawn, Sergei. I'll get onto the new version as soon as I can. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
Hi! On Feb 24, Tom Cunningham wrote: > It appears you can't combine an insert-select with an on-duplicate-key-update. You can, since 4.1.10 (and there're some problems with name resolution there, so better wait for 4.1.11 - search bugdb for details) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
Tom Cunningham <[EMAIL PROTECTED]> wrote on 02/24/2005 11:31:31 AM: > It appears you can't combine an insert-select with an on-duplicate-key-update. > > I would find it very useful if you *could* do this. I know it would be > complicate how you would handle the syntax for what to do when you hit > a duplicate key, could do this: update all the columns that are *not* > involved in duplicate-key clashes. > > This would help in situations like the following: > > --> You have a table with some columns that are unique, & other > columns which are descriptive of those unique combinations. You also > have an auto-increment key. > --> You have a lot of data you want to insert into this table, & add > new entries where you haven't before heard of that combination of > unique-keys, & otherwise update the non-unique columns. > > Possibilities: > 1. INSERT IGNORE: doesn't update the non-unique columns. > 2. REPLACE: screws up the auto-increment columns. > > Any other ideas? > > Note: the situation described is one you find when implementing > data-warehouse so-called "slowly changing dimensions". > > Tom. > According to http://dev.mysql.com/doc/mysql/en/insert.html it seems you can. What leads you to your conclusion that what you are trying to do is an illegal statement? Can you post your test statement, any error messages you get, and the results of SHOW CREATE TABLE x\G for each of the tables used in your statement? What version of MySQL are you trying to execute this statement against? ON DUPLICATE KEY only works for versions >=4.1.0 . Shawn Green Database Administrator Unimin Corporation - Spruce Pine
INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
It appears you can't combine an insert-select with an on-duplicate-key-update. I would find it very useful if you *could* do this. I know it would be complicate how you would handle the syntax for what to do when you hit a duplicate key, could do this: update all the columns that are *not* involved in duplicate-key clashes. This would help in situations like the following: --> You have a table with some columns that are unique, & other columns which are descriptive of those unique combinations. You also have an auto-increment key. --> You have a lot of data you want to insert into this table, & add new entries where you haven't before heard of that combination of unique-keys, & otherwise update the non-unique columns. Possibilities: 1. INSERT IGNORE: doesn't update the non-unique columns. 2. REPLACE: screws up the auto-increment columns. Any other ideas? Note: the situation described is one you find when implementing data-warehouse so-called "slowly changing dimensions". Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
