Solved

mysql help

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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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