DELETE
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
而
SELECT *
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
是没有问题的。
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
而
SELECT *
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
是没有问题的。
解决方案 »
- mysql存储过程查询插入问题
- 日期加一天
- Mysql insert多条记录的问题
- postgresql返回数据集取不到数是怎么回事啊
- 求sql语句
- 我想卸载redhat linux 9自带的postgresql,该怎么做?求教。
- MySql 5.0 for winXP 版本 如何从一个Sql 脚本里创建数据库及表
- myodbc 中文问题,结果是????,紧急!!
- mysql odbc 问题.
- 为什么mysql 里用!=null查找不出字符等于0的记录
- MySQL manager or server PID file could not be found!
- 求教: 如使用一条SQL得到关联表的统计数据
delete from vml
where id in (select * (
(select id
from vml as a, vml as b
where a.id = b.id
and a.type = 'Type'
and a.enddate > b.enddate))这样可以吗?
where id in
(select id
from vml as a, vml as b
where a.id = b.id
and a.type = 'Type'
and a.enddate > b.enddate)
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
DELETE a
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);
FROM vm1 a
WHERE EXISTS ( SELECT 1 FROM vm1 WHERE Id = a.Id AND `Type` = a.Type AND EndDate > a.EndDate);这个MSSQL work,但是MySQL是不Work的。我来测试一下delete from vml
where id in
(select id
from vml as a, vml as b
where a.id = b.id
and a.type = 'Type'
and a.enddate > b.enddate)
这些语法都是错误的在mysql里面
delete a from vm1 a
left join (select Id,`Type`,min(EndDate) EndDate
from vm1 group by Id,`Type`) b
on a.Id=b.Id and a.Type=b.Type and a.EndDate=b.EndDate where b.id is null;
那么1楼的那个SQL可以吗?因为这样我在外面套了一个select。
请大大指教啊。
where id in (select * from (
(select id
from vml as a, vml as b
where a.id = b.id
and a.type = 'Type'
and a.enddate > b.enddate))
可能从写法上你的语句没有问题,但是逻辑上有问题,仅仅判断ID会删除有用数据。