mysql - 有没有办法通过 "WITH"语句获得输出？

Question

这个问题已经有答案了:

MySQL syntax for Join Update (2 个回答)

已关闭 3 年前。

Question: For all countries whose government form is a 'Federal Republic' and whose official language is either 'English' or 'German', add 100,000 to their population, set their GNPOld to be equal to their current GNP, then increase their current GNP by 10,000

因此，我得到了上述 ERD 作为引用，在尝试回答这个问题时，我想知道是否需要在更新之前从表中选择列。

经过一番搜索 https://dev.mysql.com/doc/refman/8.0/en/update.html我试过这个

UPDATE country

SET Population = Population + 100000,
GNPOld = GNP,
GNP = GNP + 10000

WHERE GovernmentForm = 'Federal Republic' AND (Language = 'English' OR Language = 'German') AND IsOfficial = true;

并且

WITH GovernmentForm = 'Federal Republic' AND (Language = 'English' OR Language = 'German') AND IsOfficial = true

UPDATE country

SET ...

两者都不起作用。我猜测我的代码结构对于这种查询来说是错误的。指针或提示将不胜感激。谢谢!

Answer 1

您必须加入countryLanguage才能获取Language和IsOfficial列。

UPDATE country AS c
JOIN CountryLanguage AS cl ON c.Code = cl.CountryCode
SET c.Population = c.Population + 100000,
    c.GNPOld = c.GNP,
    c.GNP = c.GNP + 10000
WHERE c.GovernmentForm = 'Federal Republic' 
AND cl.Language IN ('English', 'German') 
AND cl.IsOfficial = 'T';

此外，IsOfficial 是一个 ENUM，而不是 bool 值。我建议使用 IN() 来测试几种可能性之一，而不是 OR。