CODEWITHSUNDEEP
mysql
user222427
user222427

Reputation:

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

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.

Upvotes: 25

Views: 28362

Answers (7)

mickmackusa
mickmackusa

Reputation: 47904

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.

Upvotes: 0

Meph
Meph

Reputation: 1

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

Upvotes: -2

Ho D. Chung
Ho D. Chung

Reputation: 51

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')

Upvotes: 3

dooplenty
dooplenty

Reputation: 124

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

Upvotes: 2

Sergey
Sergey

Reputation: 271

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

Upvotes: 27

Quassnoi
Quassnoi

Reputation: 425411

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

Upvotes: 40

Ike Walker
Ike Walker

Reputation: 65547

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)

Upvotes: 4

Related Questions