Multiple Updates using the Insert Method - Ignoring Unique Records

Question

I want to update multiple records at once using a similar method to this:

but is there a MySQL command to ignore anything that isnt a duplicate? Something like

ON DUPLICATE UPDATE ON UNIQUE IGNORE

(ive just made this code up btw)

?

Have a look at [insert-ignore-vs-insert-on-duplicate-key-update](http://stackoverflow.com/questions/548541/) — juergen d, Jan 18 '13 at 16:00
this is the exact opposite of what i want to do - he is trying to skip the duplicate entries, whereas i want to skip anything that isnt a duplicate entry. — Jimmery, Jan 18 '13 at 16:04

score 2 · Accepted Answer · answered Jan 18 '13 at 16:41

2

Why can't we use a single UPDATE command that can update MULTIPLE rows!

UPDATE mytable 
SET Col2 = CASE
  WHEN Col1 = 1 THEN 'new Value From someplace';
  WHEN Col1 = 2 THEN 'War and Peace';
  ELSE Col2
END

The ELSE Col2 is very important, otherwise you will overwrite the rest of the table with NULL.

You can add more CASE blocks to update more columns.

By doing this you can avoid the headache of INSERTING UNIQUE records.

Hope this helps.

answered Jan 18 '13 at 16:41

Jirilmon

excellent, thanks! - im unfamiliar with the CASE / WHEN / THEN syntax, but this does exactly what im trying to do! – Jimmery Jan 18 '13 at 17:01

1 Answers1