MySQL MySQL更新案例帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6734231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL update case help
提问by Sergey
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.
我只想通过匹配 ID 来批量更新基于 ID 的列表价格。
回答by Quassnoi
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.
请注意，此查询会将具有其他 ID 的记录的价格更新为NULL.
If you only want to update only ids from your list, use this:
如果您只想更新列表中的 ID，请使用以下命令：
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
回答by Sergey
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:
或者您可以使用 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:
设置 LIMIT 也是个好主意：
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
回答by Ike Walker
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)
回答by dooplenty
I'm assuming this is a mysql query. You can use the first query that Quassnoi posted and just add
我假设这是一个 mysql 查询。您可以使用 Quassnoi 发布的第一个查询并添加
WHEN ID THEN price
as the last "WHEN". This will prevent all of your price fields from being set to null
作为最后一个“WHEN”。这将防止您的所有价格字段被设置为 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
回答by Ho D. Chung
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')
回答by Meph
update ae44
set Price =
Case ID
when 'AT259793380' then '500'
when 'AT271729590' then '600'
when 'AT275981020' then '700'
end case
