Multi table delete is not working?

3 posts / 0 new
Last post
vadimtk
vadimtk's picture
Offline
Last seen: 2 months 1 day ago
Joined: Oct 30 2009
Junior Boarder

Posts: 8

Vadim Tkachenko
Multi table delete is not working?

I am trying to filter out duplicates by loading data into an preload_table,

than delete records from this preload_table which have dupicates in a main table.

 

So "m2" is the main table,

"load_m2" is preload_table.

 

I have

select count(*) from load_m2;
+----------+
| count(*) |
+----------+
|  3225321 |
+----------+

 

I check for duplicates this way:

select count(*) FROM m2,load_m2 WHERE  m2.id1=load_m2.id1 and m2.id2=load_m2.id2 and m2.id3=load_m2.id3 and m2.id4=load_m2.id4 and m2.ts=load_m2.ts;
+----------+
| count(*) |
+----------+
|  3225321 |
+----------+
1 row in set (2.84 sec)
 

So basically in load_m2 is all duplicated.

 

Now I try to delete it them as:

 DELETE FROM load_m2 USING  m2,load_m2 FROM m2,load_m2 WHERE  m2.id1=load_m2.id1 and m2.id2=load_m2.id2 and m2.id3=load_m2.id3 and m2.id4=load_m2.id4 and m2.ts=load_m2.ts;

 

Query OK, 3225321 rows affected (24.67 sec)

 

so I assume all records are deleted, but:

mysql> select count(*) from load_m2;
+----------+
| count(*) |
+----------+
|  3225321 |
+----------+
1 row in set (0.03 sec)

 

Query OK, 0 rows affected (3.42 sec)

 

 

so all records stay there ? This is not what I exected.

 

 

So it seems my method for data deduplication is not working.

What is your suggested way to prevent to insert duplicates ?

 

 

 

 

 

 

 

vadimtk
vadimtk's picture
Offline
Last seen: 2 months 1 day ago
Joined: Oct 30 2009
Junior Boarder

Posts: 8

Vadim Tkachenko
Actually it seems the data

Actually it seems the data removed from wrong table.

 

before query

 

mysql> select count(*) from m2;
+------------+
| count(*)   |
+------------+
| 5072878020 |
+------------+
1 row in set (6.42 sec)

 

After query:

mysql> select count(*) from m2;
+------------+
| count(*)   |
+------------+
| 5069652699 |
+------------+
1 row in set (4.40 sec)

 

 

But when I use

"DELETE FROM load_m2 USING  m2,load_m2"

I expect data to be delete from table "load_m2", not "m2"

 

 

Documentation

http://dev.mysql.com/doc/refman/5.1/en/delete.html

says

only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted.

 

radams
radams's picture
Offline
Last seen: 1 day 12 hours ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Multi table delete

 

Hi Vadim,

 

We have opened this as a bug and have it in the queue to be fixed right away

In the meantime the work-around is to switch the order of the tables in the from clause.

 

DELETE FROM load_m2 USING  load_m2,m2".

 

Thank you,

 

Robert