Updating table based on a second query's result

You can use If Condition in SQL to wrap an insert statement inside the If statement. An example is given below.

IF NOT EXISTS (SELECT 1 FROM myTable WHERE ID=25)
    INSERT INTO AnotherTable(column1, column2)
    VALUES(......)

Hi

Chat GPT gave me these solutions. Updating customer table based on if any orders exist in orders table for the customer.

UPDATE Customers

SET IsActive = IIF(DLookup("CustomerID", "Orders", "CustomerID=" & Customers.CustomerID) Is Not Null, True, False)

OR

UPDATE Customers

SET IsActive = True

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID

WHERE Orders.CustomerID IS NOT NULL

UPDATE Customers

SET IsActive = False

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID

WHERE Orders.CustomerID IS NULL

Thanks

Regards

You can simplify that to:

UPDATE Customers LEFT JOIN Orders

On Customers.CustomerID = Orders.CustomerID

SET IsActive = (Orders.CustomerID IS NOT NULL);

Bear in mind that the inclusion of an IsActive column in the Customers table does introduce redundancy and the possibility of update anomalies. You can return the value with a query like this without the risk of inaccurate data:

SELECT Customers.*, (Orders.CustomerID IS NOT NULL) As IsActive

FROM Customers LEFT JOIN Orders

On Customers.CustomerID = Orders.CustomerID;