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:


| Id  | Active | Username |
| 100 |      1 | bobby    |


| 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.

  • Is your goal to avoid repeating <some complicated check>? And why do you need to maintain the state in both places? It's redundant because you can always get to it by a simple join, and you also have to worry about future maintenance the other way (when you update a user back to active). Apr 11, 2019 at 15:47

2 Answers 2


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

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>;

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


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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.