SQL redshift: Updating a column with data from another table but get an error, why?-CodePudding

I am using redshift and have followed this from an example. But I get the error: [42601] ERROR: syntax error at or near "INNER" Position:

UPDATE podcast_platform_episode_level
INNER JOIN podcast_country_codes
ON podcast_platform_episode_level.country = podcast_country_codes.country
SET podcast_platform_episode_level.country_label = podcast_country_codes.country_label

CodePudding user response：

Try this

UPDATE podcast_platform_episode_level
SET country_label = podcast_country_codes.country_label
FROM podcast_country_codes
WHERE podcast_platform_episode_level.country = podcast_country_codes.country

CodePudding user response：

I renamed a column to country_code in podcast_platform_episode_level to help avoid confusion. But still surpised this code below works when the code above does not

-- adds country_label data
UPDATE podcast_platform_episode_level
   SET country_label = c.country_label
FROM podcast_country_codes c
WHERE c.country = country_code;