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