MySQL CASE to update one column from multiple columns + LIKE

Question

I was wondering if this is possible to update one column from values in different other columns... something like:

UPDATE table
    SET column1 = CASE column2,column3
        WHEN column2 == test AND column3 == 1 THEN 100
        WHEN (column2 LIKE %test1% OR column2 LIKE %test2%) AND column3 == 2 THEN 50
        ELSE 0
    END

Thanks for any answer!

EDIT : answer for others who might have the same problem:

UPDATE table
    SET column1 = CASE 
        WHEN column2 = test AND column3 = 1 THEN 100
        WHEN (column2 LIKE '%test1%' OR column2 LIKE '%test2%') AND column3 = 2 THEN 50
        ELSE 0
    END

Try removing column2, column3 after the CASE. – Edper Nov 14 '13 at 12:31 — Edper, Nov 14 '13 at 12:31
And make sure %test1% and %test2% are in quotes – OGHaza Nov 14 '13 at 12:44 — OGHaza, Nov 14 '13 at 12:44
it doesn't seem to work... :/ – Snipchain Nov 14 '13 at 13:05 — Snipchain, Nov 14 '13 at 13:05

score 1 · Accepted Answer · edited Feb 11 '14 at 21:25

1

Remove "column2, column3" after case and try following thing,

UPDATE table
    SET column1 = CASE WHEN column2 == test AND column3 == 1 THEN 100
        WHEN (column2 LIKE '%test1%' OR column2 LIKE '%test2%') AND column3 == 2 THEN 50
        ELSE 0
    END

following is the link to guide you more about CASE statement: http://dev.mysql.com/doc/refman/5.0/en/case.html

edited Feb 11 '14 at 21:25

rene

41,474
78
114
152

answered Nov 14 '13 at 12:58

Himanshu Sharma

254
1
5

MySQL CASE to update one column from multiple columns + LIKE

1 Answers1