MySQL更新案例帮助

2021-11-20 00:00:00 mysql

谁能告诉我如何解决这个查询?

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

我只想通过匹配ID来批量更新基于ID的listing价格.

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

请注意，此查询会将具有其他 ID 的记录的价格更新为 NULL.

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

如果您只想更新列表中的 ID，请使用:

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

相关文章