CODEWITHSUNDEEP
sqlamazon-web-servicesjoinsql-updateamazon-redshift
SusanD
SusanD

Reputation: 141

Redshift: UPDATE statement using a LEFT JOIN returns "table name specified more than once" error

I have an UPDATE statement in Redshift that I'd like to use the LEFT JOIN method to identify records that don't exist in the second table. The statement keeps returning the "table name specified more than once" error. I understand I can use different methods such as NOT IN with a subquery but I'd like to learn how can I adapt this script in PostgreSQL using LEFT JOIN approach. Thank you in advance.

UPDATE A  
   SET A.column_Name = 'Y' 

FROM tbl_A A
LEFT JOIN tbl_B B
ON A.Mathcing_Column_Name = B.Matching_Column_Name
WHERE B.Matching_Column_Name is NULL

Upvotes: 1

Views: 840

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use NOT EXISTS instead:

UPDATE tbl_A A  
   SET column_Name = 'Y' 
   WHERE NOT EXISTS (SELECT 1
                     FROM tbl_B B
                     WHERE A.Matching_Column_Name = B.Matching_Column_Name
                    );

Upvotes: 2

Mitko Keckaroski
Mitko Keckaroski

Reputation: 1040

Try this. (it's working)

with temp AS
(
  SELECT A.* FROM tbl_A A
  LEFT JOIN tbl_B B
  ON A.Mathcing_Column_Name = B.Matching_Column_Name
  WHERE B.Matching_Column_Name is NULL
)
UPDATE tbl_A C  SET column_Name = 'Y' 
from temp D
where C.id=D.id

Upvotes: 1

Related Questions