Multiple update question

Hi,

I have a mySql question here on updates to multiple tables.

Here's a simple schema to clarify things:

Structure

Table A
-------
Field A1
Field A2 (both int)

Table B
-------
Field B1
Field B2 (both int)

Data

Table A
-------
Row 1: (A1 - 1, A2 - 0)
Row 2: (A1 - 2, A2 - 0)

Table B
-------
Row 1: (B1 - 1, A2 - 2)
Row 2: (B1 - 1, A2 - 3)
Row 3: (B1 - 2, A2 - 4)
Row 4: (B1 - 2, A2 - 5)
Row 5: (B1 - 2, A2 - 6)

Now it is possible to group the rows in Table B in to sets where each set
has the same value of B1 (let's call these B1 groups). In our example, this
would be something like this:

B1 group with B1 field value of 1
(B1 - 1, A2 - 2)
(B1 - 1, A2 - 3)

B1 group with B1 field value of 2
(B1 - 2, A2 - 4)
(B1 - 2, A2 - 5)
(B1 - 2, A2 - 6)

I would like field A2 in Table A updated with a value that is obtained as
follows:

The query gets the value of the A1 field for the row whose A2 value it is to
update;

It looks at the B1 groups to see which group has the same B1 field value as
the A1 field value it got earlier;

It then gets the highest value for B2 for all the records in that B1 group;

It updates field A2 with this value.

For example, to update Row 1, the query will get the value of the field A1
for the row - this is 1.

It will then look at the B1 groups to see which group has the same B1 field
value as 1. There are two B1 groups here, as listed above. The first group
has all B1 fields having a value of 1. So it will pick this.

It will then look at all the B2 field values in this group, and see what the
highest value is. There are two field values - 2 and 3. So it will pick 3.

Lastly, it will update the field A2 in row 1 with this value of 3.

I hope this explains what I'm trying to achieve - sorry if it's a bit
longwinded.

I had a query that ran something like this, but it didn't quite work:

UPDATE A INNER JOIN B
ON A.A1 = B.B1
SET A.A2 = max(B.B2);

TIA,

-- 
Akin
aknak at aksoto dot idps dot co dot uk