On duplicate key delete?

Databases
1

I have a MySQL query that updates one or multiple rows in a table and the query is created by looping through various values in php. Let’s say that the table is called t and has two columns, a and b, that together constitutes the primary key. For example:


a    b
1    100
2    100
2    200
2    300

The query I have now is constructed along these lines:

UPDATE t
SET b =
    CASE
        WHEN b = 100 THEN 200
        WHEN b = 300 THEN 400
    END
WHERE b IN(100,300)

That query will obviously fail in this particular case since it will try to modify line two in a way that makes it identical to line three. What I want is a query that creates the following output, i.e. either deletes line three and modifies line two, or deletes line two and leaves line three unaffected:


a    b
1    200
2    200
2    400

Any suggestions would be greatly appreciated.

2

I should perhaps say that I have a working solution involving two queries for each update instead of one query for all updates. So what I’m looking for is a more economic solution than this one:

UPDATE t
SET b = :NEW
WHERE b = :OLD AND a NOT IN (
    SELECT a FROM (SELECT a FROM t WHERE b = :NEW) AS x
)

DELETE FROM t WHERE b = :OLD
3

use UPDATE IGNORE

4

Thanks. Beautifully simple for the first part – running all updates in one query. But I will still have to delete the remaining rows in another query (since UPDATE IGNORE will leave the a = 2/b = 100 row), or could they be combined?

5

you can also delete the duplicate key . once you have normalized the duplicate key you can avoid the errors

6

that word normalize – i do not think it means what you think it means