CODEWITHSUNDEEP
mysqlsql-delete
max fox
max fox

Reputation: 23

mysql delete successive rows

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?

Upvotes: 1

Views: 104

Answers (2)

Himanshu
Himanshu

Reputation: 32602

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.

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

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

Upvotes: 0

Related Questions