MySQL update case help

Can anybody tell me how to fix this query?

update ae44
set Price = Case
when ID = 'AT259793380' then '500'
when ID = 'AT27开发者_C百科1729590' then '600'
when ID = 'AT275981020' then '700'
end case

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

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

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

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)

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

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

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.

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