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


.

Relevant Pages

  • Return Hierarchical Data From One Table (with a JOIN to a second table)
    ... I would appreciate help in designing an efficient query that will retrieve ... [SequenceInRank] ... - specifies which row is the logical parent of the ...
    (microsoft.public.sqlserver.programming)
  • [GIT PULL] post-2.6.17-rc1 fixes
    ... Wait for join to finish before freeing mcast struct ... int ret; ... Otherwise it is a query ID that can be used to cancel ...
    (Linux-Kernel)
  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Rewrite Query for tuning.
    ... The Query returns rows. ... CONSTRAINT PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.programming)
  • Re: periods in full text
    ... If the dot is important to ... query to do this combined approach. ... Is this just how SQL Server works? ... word file says that 1 is a noise word. ...
    (microsoft.public.sqlserver.fulltext)