How do I update an entry in MySQL using CASE for the new value?

Question

Can anybody tell me how to fix this query?

update ae44
set Price = Case
when ID = 'AT259793380' then '500'
when ID = 'AT271729590' then '600'
when ID = 'AT275981020' then '700'
end case

I just want to mass update listing price based on ID by matching up ID.

better run separate queries. This one will lock the entire table — Maxim Krizhanovsky, Jul 18 '11 at 14:53
I had a similar problem, this post put me in the right direction (upvoted Quassnoi!), but be VERY careful with this, use a WHERE statement as well, otherwise the entire table will get price=NULL if it's not one of the 3 stated ID's — patrick, Jan 31 '13 at 21:52

Quassnoi · Accepted Answer · 2011-07-18T14:18:42.690

40

UPDATE  ae44
SET     price =
        CASE
        WHEN ID = 'AT259793380' THEN
                '500'
        WHEN ID = 'AT271729590' THEN
                '600'
        WHEN ID = 'AT275981020' THEN
                '700'
        END

Note than this query will update prices for the records with other ids to NULL.

If you only want to update only ids from your list, use this:

UPDATE  ae44
JOIN    (
        SELECT  'AT259793380' AS oldval, '500' AS newval
        UNION ALL
        SELECT  'AT271729590' AS oldval, '600' AS newval
        UNION ALL
        SELECT  'AT275981020' AS oldval, '700' AS newval
        ) q
ON      ae44.id = q.oldval
SET     price = q.newval

edited Jul 18 '11 at 14:18

answered Jul 18 '11 at 14:13

Quassnoi

413,100
91
616
614

I did try that, it keeps coming back with Price cannot be null (because i have it set that way) – Jul 18 '11 at 14:14
2

Might want to add en explicit "else" for any other cases. – My Other Me Jul 18 '11 at 14:16
Or maybe just add a where clause to only update the items that match your specified IDs – My Other Me Jul 18 '11 at 14:18
@Mike: this was not obvious from your query. – Quassnoi Jul 18 '11 at 14:23
okay, i'm sorry, i thought it would be cause i'm saying when columnd ID = 'whatever' price = '500' – Jul 18 '11 at 14:31
@Mike: it's obvious what to do when the id is `whatever`. It's not obvious what to do when it's not. Do you want to leave it as is, update to a default value or what? – Quassnoi Jul 18 '11 at 14:33
I would just want to leave all rows as they are and only update the rows which i have an ID value for – Jul 18 '11 at 14:34
@Mike: then use my second query. – Quassnoi Jul 18 '11 at 14:35
okay, i saw the first error it tells me 0 rows were effected. – Jul 18 '11 at 14:38
@Mike: what version of `MySQL` are you using? – Quassnoi Jul 18 '11 at 14:39
I misstypes a } for a ), so i fixed that and i got 0 rows effected, i'm using the latest version, downloaded it last week – Jul 18 '11 at 14:40
@Mike: check your data then. The query itself works as long as there are records with given ids in `ae44`. – Quassnoi Jul 18 '11 at 14:41
ugh, n/m found it, you get the answer, i need to sleep. TY – Jul 18 '11 at 14:41

score 27 · Answer 2 · answered Nov 20 '12 at 19:44

UPDATE ae44 SET
    Price = CASE
    WHEN ID = 'AT259793380' THEN '500'
    WHEN ID = 'AT271729590' THEN '600'
    WHEN ID = 'AT275981020' THEN '700'
    ELSE Price END

Or you can use WHERE:

UPDATE ae44 SET
    Price = CASE
    WHEN ID = 'AT259793380' THEN '500'
    WHEN ID = 'AT271729590' THEN '600'
    WHEN ID = 'AT275981020' THEN '700'
    END
WHERE 
    ID IN ('AT259793380', 'AT271729590', 'AT275981020')

And set the LIMIT is good idea too:

UPDATE ae44 SET
    Price = CASE
    WHEN ID = 'AT259793380' THEN '500'
    WHEN ID = 'AT271729590' THEN '600'
    WHEN ID = 'AT275981020' THEN '700'
    END
WHERE 
    ID IN ('AT259793380', 'AT271729590', 'AT275981020')
LIMIT 3

score 4 · Answer 3 · answered Jul 18 '11 at 14:16

4

Remove the second "case" and it will work:

UPDATE ae44
SET Price = (CASE
WHEN ID = 'AT259793380' THEN '500'
WHEN ID = 'AT271729590' THEN '600'
WHEN ID = 'AT275981020' THEN '700'
END)

answered Jul 18 '11 at 14:16

Ike Walker

64,401
14
110
109

3

Be careful with this... it will set the price of any other item in the database to NULL. Add a where statement (set price is (case...) where id in ("AT259793380","AT271729590","AT275981020")) – patrick Jan 31 '13 at 21:54

score 3 · Answer 4 · edited Sep 14 '15 at 07:18

3

You can try a simple query like:

UPDATE `table`
SET Price = ELT(field(ID,'AT259793380','AT271729590','AT275981020'), '500', '600', '700')
WHERE ID IN ('AT259793380','AT271729590','AT275981020')

edited Sep 14 '15 at 07:18

josliber

43,891
12
98
133

answered Sep 14 '15 at 06:44

Ho D. Chung

51
5

score 2 · Answer 5 · answered Oct 24 '13 at 04:33

2

I'm assuming this is a mysql query. You can use the first query that Quassnoi posted and just add

WHEN ID THEN price

as the last "WHEN". This will prevent all of your price fields from being set to null

UPDATE  ae44
    SET price =
    CASE
    WHEN ID = 'AT259793380' THEN '500'
    WHEN ID = 'AT271729590' THEN '600'
    WHEN ID = 'AT275981020' THEN '700'
    WHEN ID THEN price
    END

answered Oct 24 '13 at 04:33

dooplenty

124
7

1

+1 Cool alternative _dooplenty_, but isn't it better to restrict the update to the rows that you really want to update instead of all of them? – Armfoot Jun 29 '15 at 11:28

mickmackusa · Answer 6 · 2020-12-29T20:09:25.860

Checking the mysql error would have revealed:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'AT275981020' then '700' end case'

I don't know if you thought you could write case as an alias or what that alias could be used for. Maybe this was just bad copy-pasta from another CASE block that you found in a SELECT query.

Ultimately it was the trailing case that was the issue. The query will execute once that final word is removed, but there is more to explain.

In my own project, I would be using the more concise variant of CASE syntax with ID only written once instead of writing mostly redundant expressions in each case.

I also recommend using lowercase column names so that the sql is easier to read for humans.

UPDATE ae44 SET
Price = CASE ID
    WHEN 'AT259793380' THEN '500'
    WHEN 'AT271729590' THEN '600'
    WHEN 'AT275981020' THEN '700'
END
WHERE 
    ID IN ('AT259793380','AT271729590','AT275981020')

With the addition of the WHERE cause, only 3 rows will be whitelisted for possible modification -- as a consequence the ELSE can be safely omitted from the CASE block.

score -2 · Answer 7 · edited Jan 15 '16 at 19:00

-2

update ae44
set Price = 
Case ID
when 'AT259793380' then '500'
when 'AT271729590' then '600'
when 'AT275981020' then '700'
end case

edited Jan 15 '16 at 19:00

answered Feb 21 '13 at 18:00

Meph

1

This piece of code is a copy of the question's code with a `case ID when` syntax instead of a `case when ID =` syntax and does not answer the question, since it did not fix the syntax error for having `end case` (the last `case` was the problem) and will update all the other rows' Price field to `NULL`. – Armfoot Jul 16 '15 at 16:02

How do I update an entry in MySQL using CASE for the new value?

7 Answers7

Linked

Related