current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer
    
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. –  standard Aug 1 '14 at 20:55
up vote 1 down vote

Here is how you ...

I suppose you want to update rows based on this selection. Use the FROM clause to join in (derived) tables to 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.