56,687
社区成员
发帖
与我相关
我的任务
分享删除重复数据的写法
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
...全文
请发表友善的回复…
发表回复
sxq129601 2019-10-25
- 打赏
- 举报
是不是mysql不支持这么写法的删除重复数据
[SQL]DELETE t1
FROM
trigger_info t1
WHERE
t1.id NOT IN (
SELECT
t3.maxid
FROM
(
SELECT
MAX(t2.id) maxid
FROM
trigger_info t2
GROUP BY
t2.sysname,
t2.ip,
t2.dbtype,
t2.dbname,
t2.type,
t2.trigger_message
) t3
);
[Err] 1054 - Unknown column 't2.sysname' in 'group statement'
过眼浮云866 2019-10-25
- 打赏
- 举报
改成这样试试
DELETE t1 FROM trigger_info t1 WHERE t1.id NOT IN
(SELECT t3.maxid FROM (SELECT MAX(t2.id) maxid FROM trigger_info t2 GROUP BY t2.sysname,t2.ip,t2.dbtype,t2.dbname,t2.type,t2.trigger_message) t3) ;
DELETE t1 FROM trigger_info t1 WHERE t1.id NOT IN
(SELECT t3.maxid FROM (SELECT MAX(t2.id) maxid FROM trigger_info t2 GROUP BY t2.sysname,t2.ip,t2.dbtype,t2.dbname,t2.type,t2.trigger_message) t3) ;
sxq129601 2019-10-25
- 打赏
- 举报
还是不行，数据库版本5.7
[SQL]DELETE
FROM
trigger_info t1
WHERE
t1.id NOT IN (
SELECT
t3.maxid
FROM
(
SELECT
MAX(t2.id) maxid
FROM
trigger_info t2
GROUP BY
t2.sysname,
t2.ip,
t2.dbtype,
t2.dbname,
t2.type,
t2.trigger_message
) t3
);
[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 NOT IN (
SELECT
t3.maxid
FROM
(
SELECT
' at line 3
AHUA1001 2019-10-25
- 打赏
- 举报
不好意思，看错了。
您再试试这个。
DELETE FROM trigger_info t1 WHERE t1.id NOT IN
(SELECT t3.maxid FROM (SELECT MAX(t2.id) maxid FROM trigger_info t2 GROUP BY t2.sysname,t2.ip,t2.dbtype,t2.dbname,t2.type,t2.trigger_message) t3) ;
Dleno 2019-10-25
- 打赏
- 举报
单表删除直接用DELETE FROM
联表删除要使用DELETE 要删除数据表名或别名 FROM
5楼是对的，你执行报错是字段不存在
联表删除要使用DELETE 要删除数据表名或别名 FROM
5楼是对的，你执行报错是字段不存在
sxq129601 2019-10-24
- 打赏
- 举报
还是一样
SELECT maxid FROM (
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)
)) ;
[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 NOT IN (
SELECT maxid FROM (
SELECT
max(t2.id) maxid
FROM
' at line 3
AHUA1001 2019-10-24
- 打赏
- 举报
DELETE
FROM
trigger_info t1
WHERE
t1.id NOT IN (
SELECT maxid FROM (
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)
)) ;
