在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()))

回答:

您可以UPDATEJOIN

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

回到顶部