Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

mysql help

Posted on 2015-01-27
4
Medium Priority
?
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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