Posted by J.O. Aho on 11/01/05 09:14

Epetruk wrote:
> Hm...
>
> No joy yet... I'll try crossposting to alt.php.

Cross posting to alt.php.sql had been a lot better.

> Epetruk wrote:
>> 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,
>
>

[Back to original message]