T-SQL pozwala na aktualizację istniejących w tabelach danych za pomocą instrukcji UPDATE. Lekcja ta będzie oparta o przykłady z danymi w tabelach Sales.MyCustomers, Sales.MyOrders i Sales.MyOrderDetail. Tabele te będą kopiami tabel z bazy danych TSQL2012. Aby wykonać kopie należy uruchomić poniższy kod:

IF OBJECT_ID('Sales.MyOrderDetails', 'U') IS NOT NULL
DROP TABLE Sales.MyOrderDetails;
IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL
DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL
DROP TABLE Sales.MyCustomers;
SELECT * INTO Sales.MyCustomers FROM Sales.Customers;
ALTER TABLE Sales.MyCustomers
ADD CONSTRAINT PK_MyCustomers PRIMARY KEY(custid);
SELECT * INTO Sales.MyOrders FROM Sales.Orders;
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT PK_MyOrders PRIMARY KEY(orderid);
SELECT * INTO Sales.MyOrderDetails FROM Sales.OrderDetails;
ALTER TABLE Sales.MyOrderDetails
ADD CONSTRAINT PK_MyOrderDetails PRIMARY KEY(orderid, productid);

Instrukcja UPDATE

Instrukcja UPDATE jest standardem SQL i posiada następującą formę:

UPDATE <target table>
SET <col 1> = <expression 1>,
<col 2> = <expression 2>,
...,
<col n> = <expression n>
WHERE <predicate>;

W instrukcji tej wyznaczamy tabelę docelową w której chcemy dokonać zmian.  Jeżeli chcemy dokonać zmian tylko dla ograniczonej listy wierszy to używamy filtracji w klauzuli WHERE. Instrukcja UPDATE bez klauzuli WHERE aktualizuje wszystkie wiersze w tabeli. Wartości które mają być aktualizowane wstawiamy po klauzuli SET. Wyrażenia w SET mogą używać poprzednich wartości danego wiersza. Przykładowo chcemy zmodyfikować wiersze w tabeli Sales.MyOrders powiązanych z zamówieniem o identyfikatorze 10251. Poniżej zapytanie które sprawdzi jakie mamy wartości:

SELECT *
FROM Sales.MyOrderDetails
WHERE orderid = 10251;

Wynik:

orderid productid unitprice qty discount ----------- ----------- --------------------- ------ --------------------------------------- 10251 22 16,80 6 0.050 10251 57 15,60 15 0.050 10251 65 16,80 20 0.000

Poniższy kod zaktualizuje pole zniżki dodając do niego wartość 0.05 dla zamówień z identyfikatorem 10251:

UPDATE Sales.MyOrderDetails
SET discount += 0.05
WHERE orderid = 10251;

Po aktualizacji wyniki uległy zmianie:

orderid productid unitprice qty discount ----------- ----------- --------------------- ------ --------------------------------------- 10251 22 16,80 6 0.100 10251 57 15,60 15 0.100 10251 65 16,80 20 0.050

Instrukcja UPDATE i JOIN

Standard SQL nie wspiera użycia złączeń JOIN w instrukcji UPDATE lecz T-SQL tak. Idea polega na tym aby istniała możliwość aktualizacji wierszy w tabeli która odwołuje się do wierszy w innych tabelach w celu filtrowania wierszy. Przykładowo chcemy dodać 0.05 do rabatu dla zamówień połączonych z klientami z Norwegii. Wiersze które chcemy modyfikować są w tabeli Sales.MyOrderDetails a informacje potrzebne do ich odfiltrowania są w tabeli Sales.MyCustomer. Zapytanie pobierające wyznaczone dane wygląda tak:

SELECT OD.*
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';

Aby zaktualizować dane pobrane w poprzednim zapytaniu należy zastąpić klauzulę SELECT przez klauzulę UPDATE wskazującą alias tabeli do aktualizacji:

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

Aktualizując dane w klauzuli SET jako wartości można użyć wyrażeń zawierających dane ze wszystkich tabel w wyrażeniu źródłowym, lecz można modyfikować dane tylko jednej tabeli na raz.

Niedeterministyczne działanie klauzuli UPDATE

Trzeba mieć na uwadze to, że klauzula UPDATE oparta na złączeniach JOIN może być niedeterministyczna. Dotyczy to przypadków kiedy kilka wierszy z zapytania źródłowego pasuje do jednego wiersza docelowego. Niestety SQL Serwer nie generuje w takim przypadku błędu ani ostrzeżenia. Zamiast tego SQL Serwer dokonuje niedeterministycznej aktualizacji w której jeden z wierszy źródłowych "wygrywa". Zamiast użycia niedeterministycznej aktualizacji UPDATE bazującej na złączeniach JOIN można użyć instrukcji MERGE która zawsze jest deterministyczna i generuje błąd jeżeli wiele wierszy źródłowych pasuje do jednego wiersza docelowego. Przykładowo poniższe zapytania dopasowuje klientów z ich zamówieniami zwracając kod pocztowy klienta i kod pocztowy dla powiązanego zamówienia:

SELECT C.custid, C.postalcode, O.shippostalcode
FROM Sales.MyCustomers AS C
INNER JOIN Sales.MyOrders AS O
ON C.custid = O.custid
ORDER BY C.custid;

Wynik:

custid postalcode shippostalcode ----------- ---------- -------------- 1 10092 10154 1 10092 10156 1 10092 10155 1 10092 10154 1 10092 10154 1 10092 10154 2 10077 10182 2 10077 10181 2 10077 10181 2 10077 10180 ...

Każdy wiersz klienta jest powielony w wyniku dla każdego pasującego zamówienia. Oznacza to, że kod pocztowy klienta jest powtórzony tak wiele razy do ilu pasuje zamówień. Istnieje tylko jeden kod pocztowy dla każdego klienta. Kod dostawy jest połączony z zamówieniem, dlatego może być wiele kodów wysyłki dla każdego klienta. Mając to na uwadze rozważmy poniższą instrukcję UPDATE:

UPDATE C
SET C.postalcode = O.shippostalcode
FROM Sales.MyCustomers AS C
INNER JOIN Sales.MyOrders AS O
ON C.custid = O.custid;

Istnieje 89 klientów którzy posiadają dopasowane zamówienia, niektórzy posiadają po kilka dopasowań. SQL Serwer nie wygeneruje błędu , zamiast tego za każdym razem wybierze który wiersz będzie brany pod uwagę przy aktualizacji i zwróci standardowy komunikat występujący przy aktualizacji:

(89 row(s) affected)

Zaleca się używać zawsze deterministycznych aktualizacji. Np dla tego zapytania możemy pobrać zawsze pierwszy pasujący kod pocztowy jak w zapytaniu poniżej:

UPDATE C
SET C.postalcode = A.shippostalcode
FROM Sales.MyCustomers AS C
CROSS APPLY (SELECT TOP (1) O.shippostalcode
FROM Sales.MyOrders AS O
WHERE O.custid = C.custid
ORDER BY orderdate, orderid) AS A;

Instrukcja UPDATE i wyrażenia tablicowe

W T-SQL można modyfikować dane używając wyrażeń tablicowych takich jak np CTE. Załóżmy, że chcemy zmodyfikować kraje i kody pocztowe tabeli Sales.MyCustomers danymi z odpowiadających wierszy w tabeli Sales.Customers. Pierw przyjrzyjmy się zapytaniu SELECT pobierającemu te dane:

SELECT TGT.custid,
TGT.country AS tgt_country, SRC.country AS src_country,
TGT.postalcode AS tgt_postalcode, SRC.postalcode AS src_postalcode
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid;

Wynik:

custid tgt_country src_country tgt_postalcode src_postalcode ----------- --------------- --------------- -------------- -------------- 1 Germany Germany 10154 10092 2 Mexico Mexico 10182 10077 3 Mexico Mexico 10211 10097 4 UK UK 10240 10046 5 Sweden Sweden 10269 10112 6 Germany Germany 10301 10117 7 France France 10331 10089 8 Spain Spain 10360 10104 9 France France 10368 10105 10 Canada Canada 10131 10111 ...

Aby wykonać aktualizację należy zamienić instrukcje SELECT na UPDATE jak poniżej:

UPDATE TGT
SET TGT.country = SRC.country,
TGT.postalcode = SRC.postalcode
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid;

Alternatywnym sposobem jest zdefiniowanie wyrażenia tablicowego bazującego na zapytaniu i wykonanie na nim modyfikacji. Poniższy kod demonstruje jak można tego dokonać za pomocą CTE:

WITH C AS
(
SELECT TGT.custid,
TGT.country AS tgt_country, SRC.country AS src_country,
TGT.postalcode AS tgt_postalcode, SRC.postalcode AS src_postalcode
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid
)
UPDATE C
SET tgt_country = src_country,
tgt_postalcode = src_postalcode;

Za pomocą tabeli pochodnej (DERIVED), zapytanie wygląda tak:

UPDATE D
SET tgt_country = src_country,
tgt_postalcode = src_postalcode
FROM (
SELECT TGT.custid,
TGT.country AS tgt_country, SRC.country AS src_country,
TGT.postalcode AS tgt_postalcode, SRC.postalcode AS src_postalcode
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid
) AS D;

Wróćmy do wyrażenia UPDATE bazującego na złączeniach JOIN:

UPDATE TGT
SET TGT.country = SRC.country,
TGT.postalcode = SRC.postalcode
FROM Sales.MyCustomers AS TGT
INNER JOIN Sales.Customers AS SRC
ON TGT.custid = SRC.custid;

Co ciekawe, jeżeli napisać instrukcję UPDATE z tabelą A w klauzuli UPDATE i tabelą B w klauzuli FROM otrzymamy domyślnie CROSS JOIN między A i B. Jeżeli dodamy filtr z predykatem porównującym odpowiednie elementy z obu tabel to otrzymamy logiczny odpowiednik INNER JOIN. Bazując na tym stwierdzeniu napiszmy równoważną instrukcję UPDATE do poprzedniej:

UPDATE Sales.MyCustomers
SET MyCustomers.country = SRC.country,
MyCustomers.postalcode = SRC.postalcode
FROM Sales.Customers AS SRC
WHERE MyCustomers.custid = SRC.custid;

Jest to równoważne do:

UPDATE TGT
SET TGT.country = SRC.country,
TGT.postalcode = SRC.postalcode
FROM Sales.MyCustomers AS TGT
CROSS JOIN Sales.Customers AS SRC
WHERE TGT.custid = SRC.custid;

Użycie aktualizacji z pomocą wyrażeń tablicowych jest pomocne również wtedy gdy nie możemy zmodyfikować normalnie danych zabronionych w klauzuli SET. Przykładowo funkcje okna nie mogą być używane w klauzuli SET ale można ich użyć w zapytaniu wewnętrznym wyrażenia tablicowego.

Instrukcja UPDATE bazująca na zmiennej

Czasem trzeba modyfikować wiersz i gromadzić wynik modyfikowanych kolumn do zmiennych. Można to zrobić za pomocą kombinacji instrukcji UPDATE i SELECT, lecz trzeba odwiedzić każdy wiersz dwukrotnie. T-SQL posiada specjalną składnię UPDATE która pozwala zrealizować to zadanie używając jednej deklaracji. Przykładowo przeanalizujmy stan zamówień o numerze 10250 i produkcie o numerze 51:

SELECT *
FROM Sales.MyOrderDetails
WHERE orderid = 10250
AND productid = 51;

Wynik:

orderid productid unitprice qty discount ----------- ----------- --------------------- ------ --------------------------------------- 10250 51 42,40 35 0.150

Załóżmy, że musimy zmienić wiersz poprzez zwiększenie pola rabatu o 0.05 i zapisanie nowej wartośći do zmiennej. Można to zrealizować w ten sposób:

DECLARE @newdiscount AS NUMERIC(4, 3) = NULL;
UPDATE Sales.MyOrderDetails
SET @newdiscount = discount += 0.05
WHERE orderid = 10250
AND productid = 51;
SELECT @newdiscount;

Zapytanie SELECT zwróci następujący wynik:

--------------------------------------- 0.200

Aktualizacja All-at-Once

Koncepcja All-at-Once polega na tym, że wyrażenia które biorą udział w tej samej fazie logicznego przetwarzania zapytania są przeliczane koncepcyjnie w tym samym czasie. Stwórzmy tabelę dbo.T1:

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL
);
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(1, 100, 0);

Zaktualizujmy teraz dane tak jak w poniższym zapytaniu:

DECLARE @add AS INT = 10;
UPDATE dbo.T1
SET col1 += @add, col2 = col1
WHERE keycol = 1;
SELECT * FROM dbo.T1;

Wynik:

keycol col1 col2 ----------- ----------- ----------- 1 110 100

Wszystkie obliczenia wykonywane są na oryginalnej wartości wiersza, niezależnie od kolejności. Wyrażenie col2 = col1 nie otrzymuje wartości col1 po zmianie.

Ćwiczenia

1. Stwórz tabelę Sales.MyCustomers i wypełnij ją danymi za pomocą komendy:

   IF OBJECT_ID('Sales.MyCustomers') IS NOT NULL DROP TABLE Sales.MyCustomers;
   CREATE TABLE Sales.MyCustomers
   (
   custid INT NOT NULL
   CONSTRAINT PK_MyCustomers PRIMARY KEY,
   companyname NVARCHAR(40) NOT NULL,
   contactname NVARCHAR(30) NOT NULL,
   contacttitle NVARCHAR(30) NOT NULL,
   address NVARCHAR(60) NOT NULL,
   city NVARCHAR(15) NOT NULL,
   region NVARCHAR(15) NULL,
   postalcode NVARCHAR(10) NULL,
   country NVARCHAR(15) NOT NULL,
   phone NVARCHAR(24) NOT NULL,
   fax NVARCHAR(24) NULL
   );
   INSERT INTO Sales.MyCustomers
   (custid, companyname, contactname, contacttitle, address,
   city, region, postalcode, country, phone, fax)
   VALUES(22, N'', N'', N'', N'', N'', N'', N'', N'', N'', N''),
   (57, N'', N'', N'', N'', N'', N'', N'', N'', N'', N'');
   

2. Napisz instrukcję UPDATE która zaktualizuje dane zgodnie z danymi z tabeli Sales.Customers:

   UPDATE TGT
   SET TGT.custid = SRC.custid ,
   TGT.companyname = SRC.companyname ,
   TGT.contactname = SRC.contactname ,
   TGT.contacttitle = SRC.contacttitle,
   TGT.address = SRC.address ,
   TGT.city = SRC.city ,
   TGT.region = SRC.region ,
   TGT.postalcode = SRC.postalcode ,
   TGT.country = SRC.country ,
   TGT.phone = SRC.phone ,
   TGT.fax = SRC.fax
   FROM Sales.MyCustomers AS TGT
   INNER JOIN Sales.Customers AS SRC
   ON TGT.custid = SRC.custid;
   

3. Napisz instrukcję UPDATE która zaktualizuje dane zgodnie z danymi z tabeli Sales.Customers i będzie używała CTE:

   WITH C AS
   (
   SELECT
   TGT.custid AS tgt_custid , SRC.custid AS src_custid ,
   TGT.companyname AS tgt_companyname , SRC.companyname AS src_companyname ,
   TGT.contactname AS tgt_contactname , SRC.contactname AS src_contactname ,
   TGT.contacttitle AS tgt_contacttitle, SRC.contacttitle AS src_contacttitle,
   TGT.address AS tgt_address , SRC.address AS src_address ,
   TGT.city AS tgt_city , SRC.city AS src_city ,
   TGT.region AS tgt_region , SRC.region AS src_region ,
   TGT.postalcode AS tgt_postalcode , SRC.postalcode AS src_postalcode ,
   TGT.country AS tgt_country , SRC.country AS src_country ,
   TGT.phone AS tgt_phone , SRC.phone AS src_phone ,
   TGT.fax AS tgt_fax , SRC.fax AS src_fax
   FROM Sales.MyCustomers AS TGT
   INNER JOIN Sales.Customers AS SRC
   ON TGT.custid = SRC.custid
   )
   UPDATE C
   SET tgt_custid = src_custid ,
   tgt_companyname = src_companyname ,
   tgt_contactname = src_contactname ,
   tgt_contacttitle = src_contacttitle,
   tgt_address = src_address ,
   tgt_city = src_city ,
   tgt_region = src_region ,
   tgt_postalcode = src_postalcode ,
   tgt_country = src_country ,
   tgt_phone = src_phone ,
   tgt_fax = src_fax;
   

Podsumowanie

1. T-SQL wspiera standardową instrukcję UPDATE służącą do aktualizacji danych.
2. Można modyfikować dane tylko jednej tabeli używając danych z innych tabeli połączonych za pomocą JOIN. Połączenia takie mogą sprawić, że zapytanie będzie niedeterministyczne.
3. T-SQL pozwala na użycie wyrażeń tablicowych do aktualizacji danych.
4. Można używać specjalnej formy aktualizacji aby przechwycić zmianę do zmiennej.