0

I have my database in the following order

id  date        time        user_id     visits
---------------------------------------------------------------
1   02/19/13    1361257200  xxxxxxxxx   4699
2   02/20/13    1361343600  xxxxxxxxx   7264
3   02/21/13    1361430000  xxxxxxxxx   5281
4   02/21/13    1361430000  xxxxxxxxx   5281
5   02/22/13    1361516400  xxxxxxxxx   5473

As you can see I have row with id 3 is the same as the row 4 with id 4. I want to delete one of them ONLY (they both should have the same values date, time, user_id, and visits) I also have many other duplicated rows in my data base (hundreds). I have no idea about the query that I have to use for such thing. I tried searching for some solution but they involve having the same id or copying the rows to a new table. Note: I don't have any problem even if I will have to run this query multiple times.

enter image description here

Cœur
  • 37,241
  • 25
  • 195
  • 267
Max Pain
  • 1,217
  • 7
  • 19
  • 33
  • 1
    Silly question, but if you want to delete a specific row, and not another, why not just delete by ID? – j5Dev Apr 08 '13 at 22:24
  • 1
    @j5Dev because there can be millions of such rows :) – barbashov Apr 08 '13 at 22:25
  • You can do it using a temporary table: http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table – barbashov Apr 08 '13 at 22:26
  • 1
    "I have my database in the following order". No you don't. Databases and database tables do not have an inherent order. – Oswald Apr 08 '13 at 22:27
  • 1
    It won't solve your current problem, but remember to put UNIQUE keys on the future to avoid duplication such as this one. – Lumbendil Apr 08 '13 at 22:29
  • OK, if ID isnt an option, see my answer below, it also solves your lack of a unique key. – j5Dev Apr 08 '13 at 22:29

2 Answers2

2

What you could do, if the ID way is not an option, is to add a unique key to the table.

Such as.....

ALTER IGNORE TABLE  `tablename` ADD UNIQUE `keyname` (`date` ,`time` , `visits`);

As the key is added, the table will iterate through the rows, and drop any that match, (Duplicate ones). The downside to this is that it will prevent any duplicated rows from appearing again..... that may or may not be a problem depending on the tables use.

j5Dev
  • 2,022
  • 14
  • 18
  • Lest say if I go to phpmyadmin and click on the UNIQUE button on each column. will that solve the problem in the future ? – Max Pain Apr 08 '13 at 22:39
  • Basically I use another script to copy the data for this table form another table. I will delete this one and copy the date again and put the UNIQUE. – Max Pain Apr 08 '13 at 22:41
  • It won't solve your problem. You will be creating unique keys on each column. What you want is a unique key an all columns combined. Additionally, the query `ALTER TABLE tablename ADD UNIQUE ...` will fail, because you have duplicate entries. You really need `ALTER IGNORE TABLE tablename ADD UNIQUE` to drop the duplicate entries. – Oswald Apr 08 '13 at 22:43
  • I added a picture to my question. Should I use the method as in first picture or the second ? – Max Pain Apr 08 '13 at 22:50
  • You want to use the first picture, it is a key based across the three columns where all columns match as one. It does work, as my example shows the IGNORE instruction. – j5Dev Apr 08 '13 at 23:11
2
DELETE t2
FROM `your_table` AS t1
INNER JOIN `your_table` AS t2
  ON t1.date = t2.date
  AND t1.time = t2.time
  AND t1.user_id = t2.user_id
  AND t1.visits = t2.visits
  AND t2.id > t1.id
Oswald
  • 31,254
  • 3
  • 43
  • 68