MySQL Update Query help

[ashar5]

Hi,

Assume, I have a following table whose schema is,

create table testing (test1 integer not null, test2 integer not null, test3 integer not null, test4 integer not null);

And assume the following details are inserted:

insert into testing values (1,1,0,0);
insert into testing values (1,1,1,0);
insert into testing values (1,1,2,0);
insert into testing values (1,2,0,0);
insert into testing values (1,2,1,0);
insert into testing values (1,2,2,0);
insert into testing values (1,2,3,0);
insert into testing values (1,3,0,0);
insert into testing values (1,4,0,0);
insert into testing values (1,4,1,0);

mysql> select * from testing;
+-------+-------+-------+-------+
| test1 | test2 | test3 | test4 |
+-------+-------+-------+-------+
| 1 | 1 | 0 | 0 |
| 1 | 1 | 1 | 0 |
| 1 | 1 | 2 | 0 |
| 1 | 2 | 0 | 0 |
| 1 | 2 | 1 | 0 |
| 1 | 2 | 2 | 0 |
| 1 | 2 | 3 | 0 |
| 1 | 3 | 0 | 0 |
| 1 | 4 | 0 | 0 |
| 1 | 4 | 1 | 0 |
+-------+-------+-------+-------+
10 rows in set (0.00 sec)

Here, I want to update the column test4 as 1 for the following scenario with a single update query.

1. where test1=1 and test2=1 and test3=2
2. where test1=1 and test2=2 and test3=3
3. where test1=1 and test2=3 and test3=0
4. where test1=1 and test2=4 and test3=1

ie, need to update which has maximum value in test3 column and also group by test1 and test2..

Is there a way to update it in a single update Query..

Thanks in Advance.

Regards,
Ashar

[Ancient Dragon]

You have already almost written it yourself. Groups are done on queries, not updates.

UPDATE table 
SET test4=1
WHERE (test1=1 and test2=1 and test3=2) OR
      (test1=1 and test2=2 and test3=3) OR
      (test1=1 and test2=3 and test3=0) OR
      (test1=1 and test2=4 and test3=1)