3 ms·
UPDATE FROM is a nice extension. In Oracle I've done similar things with an UPSERT/MERGE statement. The linked example of: UPDATE inventory SET quantity
by wfriesen 6y ago
UPDATE FROM is a nice extension. In Oracle I've done similar things with an UPSERT/MERGE statement. The linked example of:
UPDATE inventory
SET quantity = quantity - daily.amt
FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
WHERE inventory.itemId = daily.itemId;
Could be done with MERGE using:
MERGE INTO inventory i
USING (
SELECT
SUM(quantity) AS amt
, itemId
FROM sales
GROUP BY 2
) daily
ON (
i.itemId = daily.itemId
)
WHEN MATCHED THEN UPDATE
SET i.quantity = i.quantity - daily.amt;