CODEWITHSUNDEEP
mysqlsql
Bzyzz
Bzyzz

Reputation: 173

SQL query UPDATE multiple fields

What is wrong with this query?

UPDATE user166x136
SET desc = CASE id
WHEN 18 THEN 'apple' WHEN 14 THEN 'banana' WHEN 21 THEN 'pear' WHEN 17 THEN 'orange' WHEN 19 THEN 'lemon' 
          END
WHERE id IN (18,14,21,17,19)

id is INT
desc is VARCHAR

Upvotes: 0

Views: 40

Answers (2)

John Conde
John Conde

Reputation: 219924

DESC is a reserved keyword. If you're going to use it you must wrap it in ticks.

SET `desc` = CASE id

You're also missing a quote before orange

WHEN 17 THEN orange'
           ^^^^
           HERE

It should be

WHEN 17 THEN 'orange'

And lastly, as pointed out by Minh, you're missing the CASE in END CASE

END CASE

So you have a hat trick of errors.

Upvotes: 1

Minh Quy
Minh Quy

Reputation: 665

I think you missing END CASE keyword

UPDATE user166x136
SET `desc` = CASE id
            WHEN 18 THEN 'apple'
            WHEN 14 THEN 'banana'
            WHEN 21 THEN 'pear'
            WHEN 17 THEN 'orange'
            WHEN 19 THEN 'lemon' 
          END CASE
WHERE id IN (18,14,21,17,19)

Upvotes: 0

Related Questions