Using subqueries in PostgreSQL

Question

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.

user330315 · Accepted Answer · 2014-08-01 20:49:00Z

1

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

answered Aug 1, 2014 at 20:49

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

standard Over a year ago

Thanks, @a_horse_with_no_name that seems to work. What I had meant I would then calculate a value into a seperatefield based on the selection where TableB/field1 is NULL.

Erwin Brandstetter · Accepted Answer · 2022-08-09 00:55:37Z

1

Here is how you ...

Select rows which are not present in other table

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.

edited Aug 9, 2022 at 0:55

answered Aug 7, 2014 at 23:45

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Using subqueries in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related