UPDATE Sales.MyOrderDetails SET discount += 0.05 WHERE orderid = 10251;
Update while joining
Only one table can be updated at a time.
UPDATE OD SET OD.discount += 0.05 FROM Sales.MyCustomers AS C INNER JOIN Sales.MyOrders AS O ON C.custid = O.custid INNER JOIN Sales.MyOrderDetails AS OD ON O.orderid = OD.orderid WHERE C.country = N'Norway';
Update on join can lead to non-deterministic result. Imagine that MyCustomers is joined with MyOrders and single customer matches a few orders. When you update column in MyCustomers using joined value from MyOrders table you don’t really know which merged row will be selected. You can use MERGE instead – it shows warning when such situation happens.
You can also use CTE to reach the same goal.
WITH CTE AS ( SELECT C.custid, address, city, country FROM Sales.MyCustomers AS C INNER JOIN Sales.MyOrders AS O ON C.custid = O.custid INNER JOIN Sales.MyOrderDetails AS OD ON O.orderid = OD.orderid WHERE C.country = N'Norway' ) UPDATE CTE SET city = N'Stavarn' WHERE city = N'Stavern';
Using CTE you can easily see the join result (selecting the part of code) before the update.
You can save the new value set in update like this:
DECLARE @newdiscount AS NUMERIC(4, 3) = NULL; UPDATE Sales.MyOrderDetails SET @newdiscount = discount += 0.05 WHERE orderid = 10250 AND productid = 51; SELECT @newdiscount;