mysql delete successive rows

Question

I have the following query:

select count(*) FROM cumul AS t1, cumul AS t2 WHERE t1.id+1 = t2.id and t2.spec_datetime-t1.spec_datetime < 0.001 and t1.id < 100000 and t1.v1-t2.v1 = 0 and t1.v2-t2.v2 =0;

I want to delete the same records with:

DELETE FROM cumul AS t1, cumul AS t2 WHERE t1.id+1 = t2.id and t2.spec_datetime-t1.spec_datetime < 0.001 and t1.v1-t2.v1 = 0 and t1.v2-t2.v2 = 0;

I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t1 , cumul AS t2 WHERE t1.id+1 = t2.id and t2.spec_datetime-t1.spec_datetime <' at line 1

How can I correct the query?

Himanshu · Accepted Answer · 2012-08-22 05:15:39Z

 DELETE t1 FROM cumul AS t1, cumul AS t2 ^^ Just add alias before FROM

So try this:

DELETE t1 FROM cumul AS t1, cumul AS t2 WHERE t1.id+1 = t2.id and t2.spec_datetime-t1.spec_datetime < 0.001 and t1.v1-t2.v1 = 0 and t1.v2-t2.v2 = 0;

Refer MySQL :: 13.2.2. DELETE Syntax for more information.

Dan Grossman · Accepted Answer · 2012-08-22 04:40:28Z

You must specify which table in the table list to delete from (even though they're the same table in this instance):

DELETE t1 FROM cumul AS t1, cumul AS t2...

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

Collectives™ on Stack Overflow

mysql delete successive rows

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related