mysql - 使用MySQL中的查询更新多列

我需要使用表'customers'中的1 CASE命令设置city_populations列，如下所示

Barrie      =   177,061
Toronto     =   2,480,000
Collingwood =   17,290
Thunder Bay =   108,359

我创建了两个查询，但它不会运行，任何建议。

UPDATE customers
SET city_population Barrie = 177061,
                    Toronto = 2480000,
                    Collingwood = 17290,
                    Thunder Bay =108359
WHERE city = 'Barrie', 'Toronto', 'Collingwood', 'Thunder Bay';

 UPDATE customers
 SET city_population = 177061, 2480000, 17290, 108359
 WHERE city = 'Barrie', 'Toronto', 'Collingwood', 'Thunder Bay';

最佳答案

试着用这样的东西

UPDATE `customers`
SET `city_population` = CASE `city`
WHEN 'Barrie' THEN 177061
WHEN 'Toronto' THEN 2480000
....
END,
WHERE `customers` IN ('Barrie', 'Toronto', ...);