Solved

mysql help

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

829 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