Solved

mysql help

Posted on 2015-01-27
4
151 Views
Last Modified: 2015-01-27
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)

Open 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 |
+------+---------+------+------+

Open 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);

Open 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; 

Open 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
0
Comment
Question by:John Bolter
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40573715
Shamelessly citing point 10 of my own article ... :) ... http://www.experts-exchange.com/Database/MySQL/A_659-Database-Developers'-Quick-Reference-to-MySQL.html

update `test` t1 inner join `test` t2 on t1.x1 = t2.x1 and t1.x2 = t2.x2 and t1.x3 is not null AND  t1.x0 <> t2.x0
set t1.x1 = concat(t1.x1,'-',t1.x0)

Open in new window

0
 

Author Comment

by:John Bolter
ID: 40573756
Thanks, it's almost right. When I execute this it gives me

mysql> select * from test;
+------+-----------+------+------+
| x0   | x1        | x2   | x3   |
+------+-----------+------+------+
|    1 | test001-1 |    1 |    2 |
|    2 | test001-2 |    1 |    2 |
|    3 | test001-3 |    1 |    2 |
|    4 | test001-4 |    1 |    2 |
|    5 | test001-5 |    1 |    3 |
|    6 | test001-6 |    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)

Open in new window


but the ones with x0 = 5 and x0=6 shouldn't be updated but they are.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40573775
Oops, I messed up the x3 condition.
update `test` t1 inner join `test` t2 on 
 t1.x1 = t2.x1 and t1.x2 = t2.x2 and t1.x3 =t3.x3
   AND  t1.x0 <> t2.x0
set t1.x1 = concat(t1.x1,'-',t1.x0)

Open in new window

0
 

Author Comment

by:John Bolter
ID: 40573786
Changed it to
and t1.x3 =t2.x3

Open in new window

and it works exactly right.
Thanks Daniel
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question