Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Multiple update question
October 30, 2005, 11:29 pm
rate this thread
I have a mySql question here on updates to multiple tables.
Here's a simple schema to clarify things:
Field A2 (both int)
Field B2 (both int)
Row 1: (A1 - 1, A2 - 0)
Row 2: (A1 - 2, A2 - 0)
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
The query gets the value of the A1 field for the row whose A2 value it is to
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
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);
aknak at aksoto dot idps dot co dot uk
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum