DELETE FROM Person
WHERE EXISTS
(
SELECT 1 FROM (
SELECT
MAX(ID) ID,
Name,Age,Address,Sex
FROM dbo.Person
GROUP BY Name,Age,Address,Sex
HAVING COUNT(1)>0) T
WHERE Person.Name=T.Name
AND Person.Age=T.Age
AND Person.Address=T.Address
AND Person.Sex=T.Sex
AND Person.ID<T.ID--如果上面使用MIN函数,这里就要改成>
)
Mysql
这时,就可以使用ON DUPLICATE KEY UPDATE,它的意思是先执行前面的Insert,如果主键重复,则执行后面的UPDATE
INSERT INTO test VALUES (1,'b4','c4') ON DUPLICATE KEY UPDATE b=VALUES(b),c=VALUES(c);
可以把上面的SQL简单的理解为:
select count(1) from test where a=1;
if count(1) > 0
UPDATE test SET b='xxx',c='xxx' WHERE a=1;
执行完,可以看到有两行收到影响(至于为什么
————————————————
版权声明:本文为CSDN博主「suanday_sunny」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/mlz_2/article/details/79923900
SELECT *
FROM [DBTest].[dbo].[t_Test]
alter table [t_Test] add test2 int
update [t_Test] set test2 =(select test from [t_Test] u where u.id=id )