Updating table based on a second query's result

Question

Hi

I have a select query on a table. Based on if any rows are returned or not in the query how can I update a boolean column in a second table?

Thanks

Regards

Answer 1

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

Answer 2

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

Answer 3

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;