Is there a way to get an output with the "WITH" statement?

Question

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

So, I am given the above ERD as reference and while trying to answer the question I am wondering if I need to select the columns from the table before updating.

After some search on https://dev.mysql.com/doc/refman/8.0/en/update.html I tried this

UPDATE country

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

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

AND

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

UPDATE country

SET ...

Both does not work. I am guessing the structure of my code to be wrong for this kind of query. A pointer or a tip will be greatly appreciated. Thank you !

There's no `Language` column in the `country` table. You need to join with `countryLanguage`. — Barmar, Jan 30 '20 at 00:01
That's not what WITH does. Forget that WITH even exists, it doesn't help you solve your problem. — hobbs, Jan 30 '20 at 00:02
`true` is not a possible value of `isOfficial`. It's an ENUM, with the values `T` and `F`. — Barmar, Jan 30 '20 at 00:02
@Barmar - You are (as always) right. I thought population will need to be taken from there but country already has it. — PM 77-1, Jan 30 '20 at 00:04
Do I join before or after the {SET} ? Does {UPDATE} has to have the two tables mentioned ? — Mahamad A. Kanouté, Jan 30 '20 at 00:04
@PM77-1 I thought the same thing, but then wondered how we'd apportion the population increase among all the cities. — Barmar, Jan 30 '20 at 00:07

score 1 · Accepted Answer · answered Jan 30 '20 at 00:07

You have to join with countryLanguage to get the Language and IsOfficial columns.

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';

Also, IsOfficial is an ENUM, not a boolean. And I recommend using IN() to test for one of several possibilities, rather than OR.

Is there a way to get an output with the "WITH" statement?

1 Answers1