UPDATE Statement using LEFT JOIN and CASE statement

Database
Enthusiast

UPDATE Statement using LEFT JOIN and CASE statement

UPDATE db.source

SET

region= CASE WHEN b.title_rw LIKE '%East%'  THEN 'East'

WHEN b.title_rw LIKE '%Central%'  THEN 'Central'

WHEN b.title_rw LIKE '%West%'  THEN 'West'

else 'Region N/A' END 

from db.source a

inner join dw.person_title b on b.person_id=a.person_id

But I keep getting "UPDATE failed. 3706: Syntax error: expected something between ')' and the 'from' keyword".

What am I doing wrong?

 

Tags (3)
2 REPLIES
Enthusiast

Re: UPDATE Statement using LEFT JOIN and CASE statement

this should work, but you'll need to join on a good key, I just used title_rw to show.

UPDATE db.source   
FROM(SEL 'Y',title_rw FROM db.source ,db.person_title p
WHERE p.person_id= db.source.person_id) D( Y,titlerw)
SET
region=
CASE WHEN title_rw LIKE '%East%' AND Y = 'Y' THEN 'East'
WHEN title_rw LIKE '%Central%' AND Y = 'Y' THEN 'Central'
WHEN title_rw LIKE '%West%' AND Y = 'Y' THEN 'West'
ELSE 'Region N/A' END
WHERE db.source.title_rw = d.titlerw

Rglass

Enthusiast

Re: UPDATE Statement using LEFT JOIN and CASE statement

Thanks Rglass! works with this corrected syntax of the "FROM" coming first.