Update to replace specific value with another except when it already exists-CodePudding

I have a table with values like this:

...
jack     |  woo
...
james    |  poo
james    |  woo
...
john     |  poo
...

There's also a UNIQUE constraint on both columns

I want to change all 'poo' to 'woo', as long as it doesn't violate the constraint, and then delete the remaining 'poo', in order to obtain this table:

...
jack     |  woo
...
james    |  woo
...
john     |  woo
...

My attempts to far for the first step:

UPDATE MyTable SET lastname='woo' WHERE lastname='poo'

Result: UNIQUE constraint failed: [..]

UPDATE MyTable SET lastname='woo' WHERE lastname='poo'
AND NOT EXISTS (SELECT 1 FROM MyTable t2 WHERE t2.lastname='woo')

Result: [..] 0 rows affected

This is an action I'm executing in sqlite-browser, and I'll probably need it only a couple dozen or so times. So the solution doesn't need to be very efficient or single-query.

Since my database isn't 'live' I can also, for example, temporarily disable constraint checking, execute my first query, and then clean up duplicates (how?)

CodePudding user response：

You should correlate the subquery:

UPDATE MyTable AS t1 
SET lastname = 'woo' 
WHERE t1.lastname = 'poo'
  AND NOT EXISTS (SELECT 1 FROM MyTable t2 WHERE (t2.firstname, t2.lastname) = (t1.firstname, 'woo'));

And then delete the remaining rows:

DELETE FROM MyTable WHERE lastname = 'poo';

See the demo.