CODEWITHSUNDEEP
sqlpostgresqlsql-updatesubquerypostgresql-9.3
standard
standard

Reputation: 115

Using subqueries in PostgreSQL

I'm only about a day into using PostgreSQL and have what is pry a quite simple question. I want to create a left outer join to search for mismatch values. I'd like from the join create a subquery which selects the null values and then updates are based on this selection. What I have for the left outer join looks like this:

SELECT TableA.field1, TableA.field2, TableB.field3
FROM 
   TableA
LEFT OUTER JOIN TableB ON TableA.field1 = TableB.field1

This works for the join. However, I can't manage to figure out how to then use a subquery to return only rows where TableB.field1 is null.

Upvotes: 0

Views: 146

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656734

Here is how you ...

I suppose you want to update rows based on this selection. Use the FROM clause to join more (derived) tables in an UPDATE:

UPDATE some_table t
SET    (field1,   field2,   field3)
   = (a.field1, a.field2, a.field3)
FROM (
   SELECT a.a_id, a.field1, a.field2, a.field3
   FROM   tablea a
   LEFT   JOIN tableb b USING (field1)
   WHERE  b.field1 IS NULL
   ) a
WHERE t.a_id = a.a_id;  -- join on the connecting column

Using the short syntax variant to update multiple columns.

Upvotes: 1

user330315
user330315

Reputation:

You don't need a subquery:

SELECT TableA.field1, TableA.field2, TableB.field1
FROM TableA
   LEFT OUTER JOIN TableB ON TableA.field1 = TableB.field1
where tableB.field1 IS NULL;

Not sure what you mean with "and then updates a based on this selection" though

Upvotes: 1

Related Questions