CODEWITHSUNDEEP
mysqlsqlsql-update
H. Ferrence
H. Ferrence

Reputation: 8116

Updating records based on the largest value on its related table

I have 2 tables

Table A is as follows:

ID NAME VALUE
1  abc  0
2  lmn  0
3  xyz  0

Table B is as follows:

ID SUB_GROUP   VALUE
1  Category 1  10
1  Category 2  4
1  Category 3  8
1  Category 4  12
3  Category 1  6
3  Category 2  14
3  Category 3  0
3  Category 4  3

I want to UPDATE Table A by setting its VALUE column to the largest VALUE in Table B by matching the ID columns but only for the values in Table B in Category2 or Category 3

What might that MySQL query look like?

Upvotes: 1

Views: 69

Answers (1)

John Woo
John Woo

Reputation: 263833

UPDATE  tableA a
        INNER JOIN
        (
            SELECT  ID, MAX(Value) max_val
            FROM    tableB
            WHERE   SUB_GROUP IN ('Category 2','Category 3')
            GROUP   BY ID
        ) b ON a.ID = b.ID
SET a.VALUE = b.Max_Val

Upvotes: 1

Related Questions