CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tomer Amir
Tomer Amir

Reputation: 1595

How to update rows that were deleted from one table in the another?

I have two tables:

  1. users with user_id, status columns
  2. changes with a user_id, type columns

I want to delete a row from changes and if type = 5, then set:

users.status = NULL Where users.user_id = changes.user_id

How can I do it?

Upvotes: 1

Views: 66

Answers (2)

Andrey Morozov
Andrey Morozov

Reputation: 7979

Try this:

-- table variable to store deleted changes
declare @deleted_changes table(user_id int, [type] int);

-- saving info from deleted changes into temp table
delete from changes 
output deleted.user_id, deleted.[type] into @deleted_changes;
-- where [some condition]

-- updating users only if corresponding changes with type = 5 were deleted
update users
set status = null
where user_id in (select user_id from @deleted_changes where [type] = 5);

Upvotes: 2

Christian Phillips
Christian Phillips

Reputation: 18769

For the DELETE, do you mean...

DELETE FROM Changes WHERE type = 5

try the following for the update....

UPDATE  u
SET     u.status = NULL
FROM    users u
        INNER JOIN changes c ON u.user_id = c.user_id

Upvotes: 0

Related Questions