22,210
社区成员
发帖
与我相关
我的任务
分享MYSQL删除重复数据的写法
SELECT *
FROM
trigger_info t1
WHERE
t1.id <> (
SELECT
max(t2.id) maxid
FROM
trigger_info t2
WHERE
ifnull(t1.sysname, 0) = ifnull(t2.sysname, 0)
AND ifnull(t1.ip, 0) = ifnull(t2.ip, 0)
AND ifnull(t1.dbtype, 0) = ifnull(t2.dbtype, 0)
AND ifnull(t1.dbname, 0) = ifnull(t2.dbname, 0)
AND ifnull(t1.type, 0) = ifnull(t2.type, 0)
AND ifnull(t1.trigger_message, 0) = ifnull(t2.trigger_message, 0)
)
--执行正常
但是改成DELETE就报错
DELETE FROM trigger_info t1 where ....
[Err] 1064 - 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 't1
WHERE
t1.id <> (
SELECT
max(t2.id) maxid
FROM
trigger_info t' at line 3
FROM
trigger_info t1
WHERE
t1.id <> (
SELECT
max(t2.id) maxid
FROM
trigger_info t2
WHERE
ifnull(t1.sysname, 0) = ifnull(t2.sysname, 0)
AND ifnull(t1.ip, 0) = ifnull(t2.ip, 0)
AND ifnull(t1.dbtype, 0) = ifnull(t2.dbtype, 0)
AND ifnull(t1.dbname, 0) = ifnull(t2.dbname, 0)
AND ifnull(t1.type, 0) = ifnull(t2.type, 0)
AND ifnull(t1.trigger_message, 0) = ifnull(t2.trigger_message, 0)
)
--执行正常
但是改成DELETE就报错
DELETE FROM trigger_info t1 where ....
[Err] 1064 - 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 't1
WHERE
t1.id <> (
SELECT
max(t2.id) maxid
FROM
trigger_info t' at line 3
...全文
