P: n/a

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  
Share this Question
P: n/a

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,  
P: n/a

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,
 
P: n/a

Try this:
update A, (select B.b1, max(B.b2) b2
from B
group by B.b1
) as x
set A.a2 = x.b2
where A.a1 = x.b1   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 1362
 replies: 3
 date asked: Oct 30 '05
