Update query with null left join condition in postgreSQL-CodePudding

I'm currently migrating from SQL Server to PostgreSQL and got confuse with update query in postgres.

I have query like this in SQL Server:

UPDATE t1
SET col1 = 'xx'
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id is null

how do you do this in postgres?

thanks in advance

CodePudding user response：

The left join is used to simulate a "NOT EXISTS" condition, so you can rewrite it to:

update table1 t1
   set col1 = 'xx'
where not exists (select *
                  from table2 t2
                  where t1.id = t2.id);

As a side note: Postgres works differently than SQL Server and in general you should not repeat the target table of an UPDATE statement in the FROM clause in Postgres.