SQL Server - How to update two tables linked by foreign key with FROM statement?

Question

I am a complete SQL novice and need some help on some basic syntax on how to perform an update on two tables which are linked by foreign keys.

Here is an example:

User

+-----+--------+----------+
| Id  | Active | Username |
+-----+--------+----------+
| 100 |      1 | bobby    |
+-----+--------+----------+

Orders

+-----+--------+-------------+--------+
| Id  | Active | OrderNumber | UserId |
+-----+--------+-------------+--------+
| 200 |      1 |         123 |    100 |
| 201 |     1  |         789 |    100 |
+-----+--------+-------------+--------+

Let's say I have a user which is used in another table as the foreign key UserId. I want to write UPDATE statements that will set the user to inactive and any associated orders using that user id to inactive as well.

Here is what I have tried so far:

-- sets the User to inactive
UPDATE User SET Active = 0
WHERE <some complicated check>

-- this is where I have problems
-- how do I set the associated orders for that particular user to inactive as well?
-- I have tried...
UPDATE Orders SET Active = 0
FROM User u, Orders o
WHERE o.UserId = u.Id

But that is clearly not quite right. I can't just use the user ids in the second UPDATE statement because I don't know what users my "complicated check" will return.

Any help would be greatly appreciated.

Answer 1

All you want to do is filter on the Active column of the User table being 0. I'd also recommend using the join syntax, but that's not necessarily relevant to your question.

UPDATE Orders 
SET Active = 0
FROM Orders o
JOIN User u on o.userID = u.ID
WHERE u.Active = 0
AND o.Active != 0

Answer 2

Assuming the goal is to not have to repeat <some complicated check>, and ignoring some significant problems with the schema (including why do you need to mark both a user and all of their orders as inactive?), you can do it this way by stuffing the users affected by the first update into a #temp table, and then joining on that to cascade the update.

CREATE TABLE #u(id int);

UPDATE dbo.[User] -- keyword, and why isn't it plural like Orders?
  SET Active = 0
  OUTPUT inserted.Id INTO #u
  WHERE Active = 1 -- don't bother updating rows already set this way
  -- AND <some complicated check>;

UPDATE o
SET o.Active = 0
FROM dbo.Orders AS o
INNER JOIN #u AS u
ON o.UserId = u.id
WHERE o.Active = 1; -- again only update rows that need to change

DROP TABLE #u;

If you don't care about repeating <some complicated check> then your second query could be:

UPDATE o
SET o.Active = 0
FROM dbo.Orders AS o
INNER JOIN dbo.[User] AS u
ON o.UserId = u.Id
WHERE o.Active = 1
  AND u.Active = 0
  --AND <some complicated check>;

You don't technically have to repeat the complicated check, but depending on how many rows might already have been made inactive in the past, you might want to do it so you aren't updating more rows than you need to. The WHERE o.Active = 1 helps mitigate that, possibly, but it depends on how much history you have and how much of those rows in the past you want to affect.

Please avoid this old tableX, tableY syntax.