CODEWITHSUNDEEP
sql-serversql-updatecommandsql-deletesql-job
KAYA
KAYA

Reputation: 49

Delete and Update in same time in mssql

I have a job on mssql server for deleting.But my wish is that update another table before deleting. I have two table which is connected by "user_id" column and my tables names are "UserInfo" and "AdvertInfo", they are connected with user_id column. I am deleting giving_advert_day after 7 days. But when it is deleted , ı would like to increase (+1) user_advert_number column. if delete in user_id, deleted 3 or 4 or etc. data from table then connected data from them will increase +3, +4 or etch. Is there a way for this? Here is my delete command 

Delete from AdvertInfo where advert_id In (
select advert_id from AdvertInfo
 where advert_begin_date <  DATEADD(day, -7, GETDATE()))

Upvotes: 0

Views: 4306

Answers (3)

Eralper
Eralper

Reputation: 6612

Merhaba Kaya, Maybe you can use SQL OUTPUT clause as follows You may already know, while executing DELETE command you can get affected data with OUTPUT clause into a variable table and as you can see in following sample you can use it later

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

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Use the query below to achieve that you want:

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]

Upvotes: 1

user9145316
user9145316

Reputation:

You can UPDATE with 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()))

BTW, your delete clause can be written as following:

Delete 
from AdvertInfo 
where advert_begin_date <  DATEADD(day, -7, GETDATE()))

There is no need for the IN predicate.

You can also create an create a trigger, to executed after delete for example to update the column the way you want.

Upvotes: 2

Related Questions