SQL Server – Update Column in One Table Based on Conditions in Another Table

querysql server

I have two tables with the following structure (rest of the columns omitted for brevity) :

tblORDERS

+-----------+-------+
|Order_ID   | Status|
+-----------+-------+
| 100       | 0     |
| 101       | 0     |
| 102       | 0     |
+-----------+-------+

tblUNITS

+------------+-------+----------+
| Unit_ID    | Status| Order_Id | 
+------------+-------+----------+
| 1          | 1     |   100    |
| 2          | 1     |   100    |
| 3          | 1     |   100    |
| 4          | 1     |   101    |
| 5          | 1     |   101    |
| 6          | 0     |   101    |
| 7          | 0     |   101    |
+------------+-------+----------+

Order_ID is a Foreign Key on tblUNITS. There can be many 'units' inside of one 'order'. I need to create an UPDATE statement that will check tblUNITS to see if all units with the same order_id are in status 1, indicating "complete" . If they are, then I want status in tblORDERS to be updated to status 1, indicating "complete".

Can anybody help me out in writing such query?

Best Answer

I think this solution will work.

--demo setup
DROP TABLE IF EXISTS tblOrders;
DROP TABLE IF EXISTS tblUnits;
CREATE TABLE tblOrders (
  Order_ID INTEGER,
  Status INTEGER
);

INSERT INTO tblOrders
  (Order_ID, Status)
VALUES
  ('100', '0'),
  ('101', '0'),
  ('102', '0');

  CREATE TABLE tblUnits (
  Unit_ID INTEGER,
  Status INTEGER,
  Order_Id INTEGER
);

INSERT INTO tblUnits
  (Unit_ID, Status, Order_Id)
VALUES
  ('1', '1', '100'),
  ('2', '1', '100'),
  ('3', '1', '100'),
  ('4', '1', '101'),
  ('5', '1', '101'),
  ('6', '0', '101'),
  ('7', '0', '101');


--solution
UPDATE o
SET o.Status = 1
FROM tblOrders o
--check to see if there are units for the order
WHERE EXISTS (  
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
            AND u.STATUS <> 1
        )
--verify update
select * from tblOrders

| Order_ID | Status |
|----------|--------|
| 100      | 1      |
| 101      | 0      |
| 102      | 0      |
Related Question