Update a record in table whose associated values in another table has a particular value

Question

I have a table, A, that has a 1-* relationship with records in table B (i.e. there can be multiple records in B that correspond to a single record in A).

Table A looks like this

ID	Header_Status
1	A
2	A
3	A

Table B looks like

ID	Line_Status
1	D
1	E
2	D
2	D

I want the header status in Table A to be updated to "G" for those records whose all the associated line status is 'D'.

For the above example, it should update second row (i.e. record with ID - 2 ) in Table A as both the records associated with it in Table B has the status as 'D' . It shouldn't update the first record (i.e. record with ID - 1 ) as it has one of the associated records in Table B with status 'E'.

I was trying this query

UPDATE A a 
INNER JOIN B b ON a.id = b.id 
SET a.header_status = 'G' 
where b.line_status = 'D';

It is not updating any rows. What can I try next?

Answer 1

Using an update join, we can try:

UPDATE TableA a
INNER JOIN
(
    SELECT ID
    FROM TableB
    GROUP BY ID
    HAVING MIN(Line_Status) = MAX(Line_Status) AND
           MIN(Line_Status) = 'D'
) b
    ON a.ID = b.ID
SET
    a.Header_Status = 'G';

The subquery aliased as b finds all ID values from the B table for which all line status values are D only. The join then restricts the A table to only these ID records, and then we update these records' header status to G.