我正试图根据一些文件删除重复的行。当我运行以下查询时:
delete
from slowmo_vid as sv1, slowmo_vid as sv2
where sv1.video_id = '2luh6g3ni5ex'
and sv1.slowmo_end_t<=sv2.slowmo_end_t;
我得到错误:
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 sv1, slowmo_vid as sv2
where sv1.video_id = '2luh6g3ni5ex'
and sv1.slowmo_end' at line 2
表的字段为:id、video_id internal_uri、slowmo_end_t
您似乎试图在DELETE
语句中执行ANSI-92样式的内部联接。但是WHERE
子句不能同时用于强制联接和对结果集强制限制。相反,请执行以下显式INNER JOIN
来删除所需的记录。请注意,WHERE
子句所起的作用是显而易见的。
更新:如果要删除包含最大video_id
的记录中除之外的所有记录,则可以向WHERE
子句添加嵌套子查询。
DELETE sv1.*
FROM slowmo_vid sv1
INNER JOIN slowmo_vid sv2 ON sv1.slowmo_end_t <= sv2.slowmo_end_t
WHERE sv1.video_id = '2luh6g3ni5ex' AND
sv1.video_id <> (SELECT x.id
FROM (SELECT MAX(t.video_id) AS id
FROM slowmo_vid t) x)
可以在DELETE语句中指定多个表来删除行根据WHERE子句。但是,不能在多表DELETE。table_references子句列出了表参与加入。其语法如第12.2.8.1节所述,"JOIN语法"。
http://dev.mysql.com/doc/refman/5.6/en/delete.html
手册中的示例是:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
不能在DELETE语句中使用别名。您可以尝试使用:
DELETE FROM myAlias USING `my_table` AS myAlias
或者尝试不使用任何别名