T-SQL – Updates

Simple update

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s