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?)
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.