2
UPDATE vaccine SET country_code = CASE
WHEN vaccine_country='Germany' THEN  country_code ='DE'
WHEN vaccine_country='United States'THEN country_code='US'
WHEN vaccine_country='China' THEN country_code='CN'
WHEN vaccine_country='Turkey' THEN country_code='TR'
WHEN vaccine_country='Russia' THEN country_code='RU'
END

I have a query like that, the enter image description herequery is working without any error but the table is not updating after executing the query

1
  • do you have values like vaccine_country='Germany', 'Russia' in vaccine table? Commented Jun 22, 2021 at 14:57

2 Answers 2

3

A CASE expressions returns a single value it's not a replacement for an assignment.

Your current THEN expressions all return a boolean expression that compares the (current) value of the column country_code with the constant on the right hand side. You need to remove the comparison with the target column in your CASE expression:

UPDATE vaccine 
  SET country_code = CASE
                      WHEN vaccine_country='Germany' THEN 'DE'
                      WHEN vaccine_country='United States' THEN 'US'
                      WHEN vaccine_country='China' THEN 'CN'
                      WHEN vaccine_country='Turkey' THEN 'TR'
                      WHEN vaccine_country='Russia' THEN 'RU'
                    END

Or a bit shorter:

UPDATE vaccine 
  SET country_code = CASE vaccine_country
                      WHEN 'Germany' THEN 'DE'
                      WHEN 'United States' THEN 'US'
                      WHEN 'China' THEN 'CN'
                      WHEN 'Turkey' THEN 'TR'
                      WHEN 'Russia' THEN 'RU'
                    END

Another option is to provide the mapping with a VALUES clause instead of a CASE expression:

UPDATE vaccine 
   SET country_code = t.country_code
FROM ( 
  values 
     ('Germany', 'DE'), 
     ('United States' , 'US'),
     ('China' , 'CN')
     ('Turkey' , 'TR')
     ('Russia' , 'RU')
) AS t(country, country_code)
where vaccine.country = t.country;

Note that for rows where the vaccine_country does not contain any of the listed countries, the country_code will be set to NULL which might or might not be what you want.

Sign up to request clarification or add additional context in comments.

Comments

1

Get rid of the country_code after the THEN

UPDATE vaccine SET country_code = CASE
  WHEN vaccine_country='Germany' THEN  'DE'
  WHEN vaccine_country='United States'THEN 'US'
  WHEN vaccine_country='China' THEN 'CN'
  WHEN vaccine_country='Turkey' THEN 'TR'
  WHEN vaccine_country='Russia' THEN 'RU'
END;

Demo: db<>fiddle

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.