mysql - 如何使用单个 mysql 查询批量更新具有多个条件或条件的多个字段或列

Question

这是我的表在查询之前的样子。

cid  | purl      | purl_full
----------------------------
2050 | dingo     | http://dingo.domain.com
2050 | wombat    | http://wombat.domain.com
2050 | platypus  | http://platypus.domain.com
2050 | koala     | http://koala.domain.com
2050 | dingo1    | http://dingo1.domain.com
2050 | wombat1   | http://wombat1.domain.com
2050 | platypus1 | http://platypus1.domain.com
2050 | koala1    | http://koala1.domain.com
2050 | dingo     | tempurl
2050 | wombat    | tempurl
2050 | platypus  | tempurl
2050 | koala     | tempurl

这就是查询后的样子。

cid  | purl      | purl_full
----------------------------
2050 | dingo     | http://dingo.domain.com
2050 | wombat    | http://wombat.domain.com
2050 | platypus  | http://platypus.domain.com
2050 | koala     | http://koala.domain.com
2050 | dingo1    | http://dingo1.domain.com
2050 | wombat1   | http://wombat1.domain.com
2050 | platypus1 | http://platypus1.domain.com
2050 | koala1    | http://koala1.domain.com
2050 | dingo2    | http://dingo2.domain.com
2050 | wombat2   | http://wombat2.domain.com
2050 | platypus2 | http://platypus2.domain.com
2050 | koala2    | http://koala2.domain.com

这就是查询后的结果。

cid  | purl      | purl_full
----------------------------
2050 | dingo     | http://dingo.domain.com
2050 | wombat    | http://wombat.domain.com
2050 | platypus  | http://platypus.domain.com
2050 | koala     | http://koala.domain.com
2050 | dingo1    | http://dingo1.domain.com
2050 | wombat1   | http://wombat1.domain.com
2050 | platypus1 | http://platypus1.domain.com
2050 | koala1    | http://koala1.domain.com
2050 | dingo2    | 
2050 | wombat2   | 
2050 | platypus2 | 
2050 | koala2    | 

这是我正在使用的查询。

UPDATE localhost_campaigns.list_data SET 
purl = (CASE 
WHEN (purl = 'dingo' AND purl_full = 'tempurl' AND cid = 2050) THEN 'dingo2' 
WHEN (purl = 'wombat' AND purl_full = 'tempurl' AND cid = 2050) THEN 'wombat2' 
WHEN (purl = 'platypus' AND purl_full = 'tempurl' AND cid = 2050) THEN 'platypus2' 
WHEN (purl = 'koala' AND purl_full = 'tempurl' AND cid = 2050) THEN 'koala2' 
END), 
purl_full = (CASE 
WHEN (purl = 'dingo' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://dingo2.domain.com' 
WHEN (purl = 'wombat' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://wombat2.domain.com' 
WHEN (purl = 'platypus' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://platypus2.domain.com' 
WHEN (purl = 'koala' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://koala2.domain.com' 
END) 
WHERE (purl IN ('dingo', 'wombat', 'platypus', 'koala') AND purl_full = 'tempurl' AND cid = 2050);

因此它会更新一个字段，但不会更新另一个字段。由于某种原因，它只是空白了第二个字段。

这更糟糕:

UPDATE localhost_campaigns.list_data SET 
purl = CASE 
WHEN (purl = 'dingo' AND purl_full = 'tempurl' AND cid = 2050) THEN 'dingo2' 
WHEN (purl = 'wombat' AND purl_full = 'tempurl' AND cid = 2050) THEN 'wombat2' 
WHEN (purl = 'platypus' AND purl_full = 'tempurl' AND cid = 2050) THEN 'platypus2' 
WHEN (purl = 'koala' AND purl_full = 'tempurl' AND cid = 2050) THEN 'koala2' 
END, 
purl_full = CASE 
WHEN (purl = 'dingo' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://dingo2.domain.com' 
WHEN (purl = 'wombat' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://wombat2.domain.com' 
WHEN (purl = 'platypus' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://platypus2.domain.com' 
WHEN (purl = 'koala' AND purl_full = 'tempurl' AND cid = 2050) THEN 'http://koala2.domain.com' 
END; 

也尝试过这个，但没有成功(使 purl 成为唯一的名称)(同样，它只更新 purl，但不更新 purl_full，或者更确切地说，它更新它但使其为空):

UPDATE localhost_campaigns.list_data 
SET purl = CASE purl 
WHEN 'temp_dingo' THEN 'dingo' 
WHEN 'temp_wombat' THEN 'wombat' 
WHEN 'temp_platypus' THEN 'platypus' 
WHEN 'temp_koala' THEN 'koala' 
END, 
purl_full = CASE purl 
WHEN 'temp_dingo' THEN 'http://dingo.domain.com' 
WHEN 'temp_wombat' THEN 'http://wombat.domain.com' 
WHEN 'temp_platypus' THEN 'http://platypus.domain.com' 
WHEN 'temp_koala' THEN 'http://koala.domain.com' 
END 
WHERE (purl IN ('temp_dingo', 'temp_wombat', 'temp_platypus', 'temp_koala') AND cid = 2050); 

顺便问一下，它是如何进行更新的？它是否先执行第 1 列的所有操作，然后再执行第 2 列的所有操作，还是同时执行两列操作，然后转到下一条记录并同时执行两列操作？

Answer 1

这就是最终的工作结果。我需要将第二个 SET CASE 中的 purl 名称更改为要更改的名称，而不是原始名称。这样就回答了这个附带问题:一次执行一列还是一次执行一行。

UPDATE localhost_campaigns.list_data 
SET purl = CASE purl 
WHEN 'temp_dingo' THEN 'dingo' 
WHEN 'temp_wombat' THEN 'wombat' 
WHEN 'temp_platypus' THEN 'platypus' 
WHEN 'temp_koala' THEN 'koala' 
END, 
purl_full = CASE purl 
WHEN 'dingo' THEN 'http://dingo.domain.com' 
WHEN 'wombat' THEN 'http://wombat.domain.com' 
WHEN 'platypus' THEN 'http://platypus.domain.com' 
WHEN 'koala' THEN 'http://koala.domain.com' 
END 
WHERE (purl IN ('temp_dingo', 'temp_wombat', 'temp_platypus', 'temp_koala') AND cid = 2050);