mysql help

Hi, I have a small mysql database and I have some corrupted data because my code was bugged. I have fixed the bug but not the corrupted data.

I need some sql to take all the duplicate values of x1 that have duplicate values of x2 and x3 and rename them to something so they are unique. If x3 is null, nothing should be done.

So this is before created with some data

mysql>
mysql> select * from test;
+------+---------+------+------+
| x0   | x1      | x2   | x3   |
+------+---------+------+------+
|    1 | test001 |    1 |    2 |
|    2 | test001 |    1 |    2 |
|    3 | test001 |    1 |    2 |
|    4 | test001 |    1 |    2 |
|    5 | test001 |    1 |    3 |
|    6 | test001 |    2 |    2 |
|    7 | test001 |    1 | NULL |
|    8 | test001 |    1 | NULL |
|    9 | test001 |    2 | NULL |
|   10 | test002 |    2 | NULL |
+------+---------+------+------+
10 rows in set (0.00 sec)

Select allOpen in new window

and what I would like it to look like after execution of some sql might be (note the suffix a, b, c, d but I actually don't care what the new value is, just that it isn't test001).

+------+---------+------+------+
| x0   | x1      | x2   | x3   |
+------+---------+------+------+
|    1 | test001a|    1 |    2 |
|    2 | test001b|    1 |    2 |
|    3 | test001c|    1 |    2 |
|    4 | test001d|    1 |    2 |
|    5 | test001 |    1 |    3 |
|    6 | test001 |    2 |    2 |
|    7 | test001 |    1 | NULL |
|    8 | test001 |    1 | NULL |
|    9 | test001 |    2 | NULL |
|   10 | test002 |    2 | NULL |
+------+---------+------+------+

Select allOpen in new window

I have created the test data with the following sql

create table test(x0 int, x1 varchar(10), x2 int, x3 int);

insert into test(x0, x1, x2, x3)
 values(1, 'test001', 1, 2);
insert into test(x0, x1, x2, x3)
 values(2, 'test001', 1, 2);
insert into test(x0, x1, x2, x3)
 values(3, 'test001', 1, 2);
insert into test(x0, x1, x2, x3)
 values(4, 'test001', 1, 2);
insert into test(x0, x1, x2, x3)
 values(5, 'test001', 1, 3);
insert into test(x0, x1, x2, x3)
 values(6, 'test001', 2, 2);
insert into test(x0, x1, x2, x3)
 values(7, 'test001', 1, null);
insert into test(x0, x1, x2, x3)
 values(8, 'test001', 1, null);
insert into test(x0, x1, x2, x3)
 values(9, 'test001', 2, null);
insert into test(x0, x1, x2, x3)
 values(10, 'test002', 2, null);

Select allOpen in new window

What I don't want is a solution that relies on me executing a zillion statements like

update test set x1=test001a' where x0=1; 

Select allOpen in new window

etc

because this is contrived data. In the real data I don't want to look at it first, I just want to run the sql and for it to work out the duplicates, not have be pre-identify them and know the x0 id's.

Thank you

John