|
|
Posted by Epetruk on 11/01/05 00:08
Hm...
No joy yet... I'll try crossposting to alt.php.
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,
Navigation:
[Reply to this message]
|