Solved

mysql help

Posted on 2015-01-27
4
139 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now