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 xxxxx\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
