I have the following postgresql database table A:

I have the following postgresql database table A:

And I have the following table B which is a child table of A:

I want to write an UPDATE statement on table A such that if there is at least one child record in B with d='X' and at least one child record in B d='Y', then A.c should be set to the maximum value of e of those child records. Otherwise, A.c should be set to NULL.

So after the update is run, table A should look like this:

I know I need a correlated subquery, but I am having a hard time building it. How can I do it?

• sql
• postgresql
• correlated-subquery

• 1 it would be helpful to use meaningful (real or invented, doesn't matter) names instead of abstractions. E.g. a = blog post, b = comment - whatever makes it trivial to understand your needs. Providing these table schemas as create-table statements also makes it easier for folks to elaborate on what you're asking. – AD7six Commented Mar 20 at 10:40
• 1 Please also tag the dbms you're using (mysql?), as that'll directly affect the relevant options/syntax. – AD7six Commented Mar 20 at 10:41
• 1 What's the purpose of the A table? All its information already seem to exist in table B. Remember that too often redundancy leads to inconsistency. – jarlh Commented Mar 20 at 13:22

3 Answers 3

Another aggregation solution is to filter in the WHERE then look for the number of distinct d values.

Also note the change that instead of joining on the subquery (which means you only update matching rows), instead put the subquery directly in the SET so you update every row.

UPDATE A a
SET c = (
    SELECT MAX(b.e) AS e_max
    FROM B b
    WHERE b.a_id = a.id
      AND b.d IN ('X', 'Y')
    GROUP BY b.a_id
    HAVING COUNT(DISTINCT b.d) = 2
);

db<>fiddle

One of the options to do this is MERGE INTO statement using Case expression with analytic functions:

MERGE INTO tbl_a t
USING ( Select    Distinct a.id as a_id, 
                  Case When Min(b.d) Over(Partition By a.id) != Max(b.d) Over(Partition By a.id)
                       Then Max(b.e) Over(Partition By a.id)
                  End as e
        From   tbl_A a
        Left Join tbl_B b ON( b.a_id = a.id And 
                              (b.d = 'X' OR b.d = 'Y') ) 
      ) x ON(x.a_id = t.id )
WHEN MATCHED THEN UPDATE
        SET c = x.e

R e s u l t :

fiddle

As you want to overwrite all rows, you'll use a subquery (able to return NULL) so that no non-matching row gets fotten (agreeing in that with Charlieface in that);
add some aggregate functions + an HAVING to NULLify a_ids that do not respect the X-Y rule and you're good to go:

update a
set c =
(
    select max(e)
    from b
    where a_id = a.id
    group by a_id
    having count(distinct case when d in ('X', 'Y') then d end) = 2
    -- Or if d contains only Xs and Ys:
    --having min(d) = 'X' and max(d) = 'Y'
);

(and a fiddle to play with)