在mssql中同时删除和更新
我在mssql服务器上有一个工作要删除。但我希望在删除之前更新另一个表。 我有两个通过“user_id”列连接的表,我的表名是“UserInfo”和“AdvertInfo”,它们与user_id列连接。我正在删除7天后的giving_advert_day。但是当它被删除时,我想增加(+1)user_advert_number列。如果在user_id中删除,从表中删除3或4等数据,则从它们连接的数据将增加+3,+4或蚀刻。有没有办法呢? 这里是我的删除命令在mssql中同时删除和更新
Delete from AdvertInfo where advert_id In (select advert_id from AdvertInfo
where advert_begin_date < DATEADD(day, -7, GETDATE()))
回答:
您可以UPDATE
与JOIN
:
UPDATE u SET u.user_advert_number = u.user_advert_number + 1
FROM UserInfo as u
INNER JOIN AdvertInfo AS a ON u.user_id = a.user_id
WHERE a.advert_begin_date < DATEADD(day, -7, GETDATE()))
顺便说一句,你删除子句可以写成如下:
Delete from AdvertInfo
where advert_begin_date < DATEADD(day, -7, GETDATE()))
没有必要的IN
谓词。
您还可以创建一个create a trigger,以执行after delete
例如更新列你想要的方式。
回答:
使用下面的查询来实现你想要的:
DECLARE @Temp TABLE ( [user_id] INT
)
DELETE i
OUTPUT deleted.[user_id] INTO @Temp
FROM AdvertInfo i
WHERE advert_begin_date < DATEADD(day, -7, GETDATE())
UPDATE u
SET u.user_advert_number = u.user_advert_number + d.cnt
FROM UserInfo as u
INNER JOIN (
SELECT [user_id], COUNT([user_id]) AS cnt
FROM @Temp
GROUP BY [user_id]
) d ON d.[user_id] = u.[user_id]
回答:
Merhaba卡亚, 也许你可以使用SQL OUTPUT clause如下 你可能已经知道,在执行删除命令你可以得到OUTPUT子句受影响的数据到一个变量表中,并且您可以在以下示例中看到,您可以稍后使用它
DECLARE @Deleted table ( user_id int
);
DELETE FROM AdvertInfo
OUTPUT DELETED.user_id
INTO @Deleted
WHERE advert_begin_date < DATEADD(day, -7, GETDATE())
update u
set user_advert_number = isnull(user_advert_number,0) + 1
from UserInfo as u
inner join @Deleted d on d.user_id = u.user_id
以上是 在mssql中同时删除和更新 的全部内容, 来源链接: utcz.com/qa/265258.html