Multiple update question

[Epetruk]

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

[Epetruk]

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,

[J.O. Aho]

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,

[ZeldorBlat]

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