MYSQL UPDATE SET on the Same Column but with multiple WHERE Clauses

Question

With MYSQL I'm using this query:

UPDATE CustomerDetails_COPY
SET Category_ID = 10
WHERE Category_ID = 2

Thats fine but I'd like to ad 15+ more SET/WHERE to it like:

UPDATE CustomerDetails_COPY
SET Category_ID = 9  WHERE Category_ID = 3
SET Category_ID = 12 WHERE Category_ID = 4
SET Category_ID = 11 WHERE Category_ID = 5
.....

How would I add to this?

EDIT:

As Per Hunters Suggestion:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
    END
WHERE Category_ID IN (2,3,4,5)

This works Great! Thanks

Hunter McMillen · Accepted Answer · 2012-02-20T21:31:44.213

22

Something like this should work for you:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
    END
WHERE Category_ID IN (2,3,4,5)

Alternatively, as Simon suggested, you could do this to save from entering the values twice:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
        ELSE Category_ID
    END

Source: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

edited Feb 20 '12 at 21:31

answered Feb 20 '12 at 21:22

Hunter McMillen

59,865
24
119
170

2

Deleted my comment as tried this and worked, nice :) As a note, you could add "ELSE Category_ID" before "END" if you didn't want to include the WHERE – Simon at The Access Group Feb 20 '12 at 21:27
Thanks. A good point; it would save from entering the columns twice. – Hunter McMillen Feb 20 '12 at 21:29
2

With the `WHERE` clause, you're pretty much guaranteed to be able to utilize an index on Category_ID, also with [safe-updates](http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_safe-updates) enabled, you must enter a `WHERE` clause with an `UPDATE`, so `ELSE` clause might not be best choice. Stick with `WHERE`. – Marcus Adams Feb 20 '12 at 21:39
Can I use both `WHERE` and `ELSE` to help in filtering out the projection that am updating(in case of hundreds of thousands or millions of records) and to save from entering the values twice respectively? – Geek Guy Dec 03 '18 at 08:40

score 2 · Answer 2 · answered Feb 20 '12 at 21:20

2

The typical way to do this would be

UPDATE CustomerDetails_COPY SET Category_ID = 10 WHERE Category_ID = 2
UPDATE CustomerDetails_COPY SET Category_ID = 9  WHERE Category_ID = 3
UPDATE CustomerDetails_COPY SET Category_ID = 12 WHERE Category_ID = 4
UPDATE CustomerDetails_COPY SET Category_ID = 11 WHERE Category_ID = 5

Is there any reason you are avoiding this approach?

Is there any correlation between the new and old values of category_ID?

answered Feb 20 '12 at 21:20

JohnFx

34,542
18
104
162

I have over 300 different types:( – Monty Feb 20 '12 at 21:21
Why does that change anything? This is a one off query, right? So use your copy/paste feature of your computer and make a day of it. Unless of course the NEW value of any of these conflicts with the OLD value of some. Then you might need a temporary table or Hunter's solution – JohnFx Feb 20 '12 at 21:22
Most are different but some are the same. – Monty Feb 20 '12 at 21:28
Sometimes to deal with php concurrency problem it may have problem for multiple separate SQL Query to execute, since mysql and php are different software, when php execute SQL Query and mysql return the results to php may have a very short delay before execute the next SQL Query. Of coz you can use Transaction to lock the table and execute at once, but some DB Engine just don't support Transaction, Example MyISAM Engine. So i think there is why the question post here for execute them in one query – Jacky Chong Jan 27 '21 at 16:49

score 1 · Answer 3 · edited Oct 16 '17 at 08:38

1

you could create another table where the mapping is made.. like:

map_table
-----------
mani_id
new_id

then fill it in...

then

UPDATE CustomerDetails_Copy set category_id = (select new_id from map_table where mani_id = category_id )

edited Oct 16 '17 at 08:38

Community

1
1

answered Feb 20 '12 at 21:24

Randy

16,480
1
37
55

MYSQL UPDATE SET on the Same Column but with multiple WHERE Clauses

3 Answers3

Linked

Related