I am trying to update a link table (table with two primary keys) where I want to update all rows where the first primary key is a set value (for example, change key1 from 10 to 20), but I only want to update these where the resulting primary key does not already exist in the table (otherwise an error is thrown on a duplicate key and the remaining rows are not updated). Using other databases, I am able to perform a subquery in the filter for the update such as the following:
UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable WHERE key1 = 20) Unfortunately, MySQL does not allow you to use a table in a subquery which is being updated. If anyone can offer any assistance with this, I would greatly appreciate it. Thanks, Bob Dankert Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
