CODEWITHSUNDEEP
mysqlselectjoinset
H. Ferrence
H. Ferrence

Reputation: 8116

How to Update a MySQL Table Based on SELECT MAX() from a JOINED Table

I have 2 MySQL Tables.

TableA
ID Total
01 0
02 0
03 0
...
99 0

TableB
ID Name Points Year
01 Joe  4      2013
01 Bill 2      2013
02 Sam  10     2013
02 Barb 1      2013
02 Joan 7      2013
...
15 Fred 0      2013
15 Jan  3      2013
...

I want to store the MAX(Points) value from Table B based on ID in the Total column in Table A

I have a MySQL Query that gets me the MAX(Points). Rather than coding a separate UPDATE query while looping through the results set from my MAX() query, I am wondering if I can accomplish this all in one MySQL query using a Join and a SET SELECT of some sort.

Is this possible to consolidate the 2 queries into 1?

Here is my MAX() Query:

$result = mysql_query('
    SELECT id, MAX(Points)
    FROM TableB
    WHERE year = "' . date('Y') . '"
    GROUP BY id
') or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
    mysql_query('
        UPDATE TableA
        SET Total = "' . $row[1] . '"
        WHERE id = "' . $row[0] . '"
    ') or die(mysql_error());
}
mysql_free_result($result);

Thanks

Upvotes: 2

Views: 5111

Answers (1)

nurdglaw
nurdglaw

Reputation: 2127

Try using this as your SQL statement:

UPDATE tablea
JOIN (
    SELECT id, max(points) AS total
    FROM tableb
    WHERE year=<whatever>
    GROUP BY id
) AS sub ON tablea.id=sub.id
SET tablea.total=sub.total

Upvotes: 3

Related Questions