Rozdział 11.
Modyfikacja danych

We wcześniejszym rozdziale omówione zostało pobieranie danych przy pomocy polecenia SELECT, używanie klauzuli WHERE (do ograniczania wierszy, które mają być zwrócone), korzystanie z funkcji liczbowych, łańcuchów znaków i daty. Omówiono podsumowywanie zwracanych danych przy użyciu super aggregates GROUP BY, ROLLUP i COMPUTE. Na końcu, pokazano pobieranie danych z więcej niż jednej tablicy poleceniem SELECT przy zastosowaniu operacji złączenia lub podzapytań.

Niniejszy rozdział skupia się na modyfikacji danych w tablicach SQL Servera przy użyciu poleceń INSERT, UPDATE i DELETE. Jednak, zanim zostaną omówione konkretne polecenia, należy się zapoznać z relacjami pomiędzy modyfikacjami danych a rejestracją zdarzeń w dzienniku transakcji.

Rejestracja transakcji

SQL Server śledzi zmiany zachodzące w bazie i rejestruje zmiany w dzienniku transakcji. Zarządzanie dziennikiem transakcji zostało omówione w rozdziale 4. Modyfikując dane w bazie należy mieć na uwadze, że każda zmiana zostaje zanotowana w dzienniku transakcji. Kiedy wykonywana jest operacja INSERT, kopia całego nowego wiersza jest zapisywana do dziennika transakcji; uruchamiając polecenie DELETE, kopia całego usuwanego wiersza jest również zapisywana do dziennika transakcji.

W przypadku operacji UPDATE, zapisywanie w dzienniku nie jest takie oczywiste. W przypadku niektórych operacji UPDATE SQL Server rejestruje jedynie bajty, które zostały zmienione. W wielu innych operacjach UPDATE SQL Server musi utworzyć dwa wpisy w dzienniku: całą starą (usuniętą) wersję wiersza i całą nową (wpisaną) wersję wiersza. Omawianie reguł warunkujących, w których przypadkach SQL Server rejestruje jedynie zmienione bajty a w których zapisuje w dzienniku całe wiersze, jest poza zakresem tej książki. Ilość danych, które zostają zapisane w dzienniku transakcji zależy również od liczby posiadanych indeksów, zagadnienie to zostanie omówione w rozdziale 13.

Od przedstawionych wymagań rejestrowania występują wyjątki, takie jak narzędzie BCP (Bulk Copy Program) — zobacz rozdział 19, które jest odpowiednikiem wielu poleceń INSERT. Jeżeli zostaje uruchomiona szybka wersja BCP, indywidualne wiersze nie są zapisywane w dzienniku transakcji, gdy są wpisywane do tablicy; jednak, w przypadku tablic posiadających indeksy każdy pojedynczy wiersz jest zapisywany w dzienniku transakcji. Jednak, niektóre zapisy do dziennika transakcji dokonywane są w celu rejestracji przestrzeni zajmowanej podczas operacji BCP, bez względu na to czy jest to „szybki” BCP czy też nie. W rozdziale 10 została omówiona operacja SELECT INTO, która również nie jest rejestrowana (z wyjątkiem wymienionej wcześniej alokacji przestrzeni). Niniejszy rozdział omawia polecenie TRUNCATE TABLE, które jest odpowiednikiem DELETE bez rejestrowania każdego usuwanego wiersza.

Teraz, mając już pojęcie o przebiegu procesu rejestrowania transakcji można przystąpić do zapoznania się z poleceniami, które wymuszają rejestrowanie zdarzeń. Na początku zostanie omówione polecenie INSERT.

Dla potrzeby dalszych ćwiczeń należy utworzyć kopie tablic publishers i sales. Kopie te posłużą do odświeżenia tablic po wykonaniu na nich różnych działań. Należy włączyć opcję bazy danych Select Into/Bulk Copy, zanim zostaną uruchomione poniższe polecenia.

USE pubs

GO

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', TRUE

GO

SELECT * INTO tmpPublishers FROM publishers

SELECT * INTO tmpStores FROM stores

SELECT * INTO tmpTitles FROM titles

SELECT * INTO tmpSales FROM sales

GO

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', FALSE

GO

Wpisywanie danych

Podstawowe polecenie INSERT dodaje w danym momencie jeden wiersz do tablicy, zmiennej tablicy lub poprzez widok do tablicy podstawowej. Przy pomocy różnych kombinacji podstawowego polecenia INSERT można dodać wiele wierszy stosując wybór danych z innej tablicy lub widoku lub uruchamiając procedurę składową lub funkcję. W każdym z tych przypadków należy znać strukturę tablicy, do której mają być wpisywane dane. Następująca informacja jest bardzo przydatna:

• Liczba kolumn w tablicy

• Typ danych każdej z kolumn

• Nazwy kolumn dla niektórych poleceń INSERT

• Więzy i własności kolumn takie jak identity, uniqueidentifier lub quantity > 20.

Więcej informacji na temat więzów i kolumn identity zostanie podanych w rozdziale 14.

Składnia polecenia INSERT jest następująca:

INSERT [INTO]

{table_or_view}

{{[ (column_list)]

VALUES

({DEFAULT |

constat_expression} [, ...n]) |

select_statement |

execute_statement} |

DEFAULT VALUES}

table_or_view ::=

{ table_name | view_name

|rowset_function

} [,...n]

Najprostsza metoda do znalezienia liczby kolumn, wraz z ich nazwami i typem danych to skorzystanie z okna Właściwości tablicy w Enterprise Managerze (zobacz rysunek 11.1).

Aby otworzyć to okno należy:

1. Wybrać bazę danych w folderze Databases.

2. Wybrać tablicę z foldera Tables.

3. Kliknąć tablicę prawym klawiszem myszy i wybrać Właściwości.

W przypadku braku SQL Server Enterprise Managera można skorzystać z systemowej procedury składowej sp_help aby uzyskać te same informacje:

USE pubs

go

EXEC sp_help publishers

go

Rysunek 11.1. Okno Właściwości tablicy.

Część wyników powinna wyglądać podobnie do przedstawionych poniżej:

Column_name Type Computed Length

........... .......... ......... ............

pub_id char no 4

pub_name varchar no 40

city varchar no 20

state char no 2

country varchar no 30

Wyniki te pokazują nazwę kolumny, typ danych i pozycje każdej z kolumn. Informacje te są potrzebne przy budowaniu polecenia INSERT.

Do pozyskiwania informacji można również używać widoków INFORMATION_SCHEMA. Przykładowo, aby znaleźć nazwy kolumn i typy danych w poprzednim przykładzie, można uruchomić następujące zapytanie:

USE pubs

GO

SELECT * FROM INFORMATION_SCHEMA.Columns

WHERE TABLE_NAME = 'publishers'

GO

Polecenie INSERT VALUES

Najprostsza forma polecenia INSERT wymaga wartości dla każdej z kolumn tablicy, w kolejności w jakiej zostały zdefiniowane kolumny. Kolejność była przedstawiona w poprzednich przykładach. Aby wpisać pojedynczy wiersza do tablicy tmpPublishers, należy uruchomić następujące polecenie:

USE pubs

GO

INSERT INTO tmpPublishers

VALUES('9956', 'A New Publisher', 'Poulsbo', 'WA', 'USA')

Jeżeli użytkownik wykonujący to polecenie ma uprawnienie INSERT do tej tablicy i nie narusza żadnych więzów, powinien otrzymać następujący komunikat SQL Servera:

(1 row(s) affected)

Ponieważ wszystkie kolumny są typu znakowego, wszystkie wpisywane wartości ujęte są w cudzysłów. Wartości liczbowe nie są ujmowane w cudzysłów.

--> Nieograniczone[Author:AK] pola typu siatki danych używane w komunikacyjnych narzędziach wizualnych nie dodają automatycznie danych do bazy. Należy napisać odpowiedni kod do przejścia przez wszystkie wartości zmienione w polach a następnie wykorzystać polecenie INSERT/VALUES aby umieścić te wiersze w bazie danych.

Najprostsza forma polecenia INSERT wymaga, aby użytkownik bezpośrednio podał wartość dla każdej z kolumn i każda wartość musi być w poprawnej kolejności. Jeżeli wartości mają być podane w innej kolejności lub dla kolumny nie ma być podawana explicite wartość, należy skorzystać z innego wariantu polecenia INSERT.

Następujące polecenie INSERT posiada listę nazw kolumn przed klauzulą VALUES i lista ta zawiera jedynie zbiór nazw kolumn w tablicy. Lista wartości VALUES powinna zawierać wartości jedynie dla podanych kolumn.

INSERT INTO tmpPublishers(state, pub_id)

VALUES('AK', '9932')

Kiedy aplikacje typu klient, takie jak Microsoft Visual Basic lub PowerBuilder łączą się z bazą danych aby dodać dane, używają omówionych właśnie poleceń INSERT/VALUES.

Co dzieje się z kolumnami nie wymienionymi w liście kolumn? Jeżeli dodawany jest nowy wiersz, każda z kolumn musi posiadać wartość. Jeżeli użytkownik nie określił wartości, SQL Server musi mieć możliwość samodzielnego określenia tej wartości. Aby SQL Server mógł określić wartość, każda z kolumn, nie wymieniona na liście, musi spełniać jeden z poniższych warunków:

• Kolumna posiada skojarzoną ze sobą wartość domyślną.

• Kolumna jest kolumną identity.

• Kolumna dopuszcza wartości NULL.

• Kolumna jest typu rowversion (formalnie nazywana timestamp w poprzednich wersjach SQL Servera)

Wartości domyślne i kolumny identity zostały omówione w rozdziale 14. Wartości NULL i kolumny rowversion zostały omówione w rozdziale 9.

W poprzednim poleceniu INSERT żadna wartość nie zostaje przypisana do kolumny pub_name, city i country. W tablicy publishers, kolumny pub_name i city dopuszczają wartości NULL, natomiast kolumna country posiada domyślną wartość USA. Po wykonaniu polecenia INSERT można uruchomić następujące zapytanie, aby sprawdzić czy wiersz został dodany:

SELECT * FROM tmpPublishers

WHERE pub_id = '9932'

Warto zauważyć, że występują wartości null i wartość domyślna w kolumnie country:

Pub_id Pub_name City state country

....... ......... ...... ...... ........

9932 NULL NULL AK USA

Uruchomienia polecenie INSERT, bez podania wartości kolumn, które nie spełniają ani jednego z podanych warunków zakończy się komunikatem o błędzie.

Następujące polecenie INSERT nie podaje wartości dla kolumny pub_id:

INSERT INTO tmpPublishers(pub_name, city, state)

VALUES('The Best Books', 'New Orleans', 'LA')

Server: Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'pub_id', table 'pubs.dbo.tmpPublishers';

column does not allow nulls. INSERT fails.

The statement has been terminated.

Warto zauważyć, że komunikat o błędzie nie jest całkowicie kompletny. Problemem nie jest to, że kolumna pub_id nie zezwala na wartości NULL, ale również to, że nie posiada wartości domyślnej i nie jest kolumną rodzaju identity.

Wartości domyślne

Można używać jeszcze innego rodzaju prostego polecenia INSERT dotyczącego pojedynczego wiersza. Stosuje się go, gdy w poleceniu nie ma być umieszczana lista nazw kolumn, a SQL Server ma skorzystać z wartości domyślnych jeżeli istnieją (dotyczy to również wartości Null i identity). Można skorzystać ze słowa kluczowego DEFAULT w bieżącej liście wartości, jako sposób na poinformowanie SQL Servera, że ma zadecydować jaką wartość wybrać w danej kolumnie. Warto rozważyć przykład:

INSERT INTO tmpPublishers

VALUES('9950', DEFAULT, DEFAULT,'AK', DEFAULT)

Jeżeli każda z kolumn w tablicy posiada jakiegoś rodzaju wartość domyślną, z której może skorzystać SQL Server, można wykorzystać jeszcze jedną odmianę polecenia. Można bezpośrednio wskazać SQL Serverowi, aby użył wszystkich domyślnych wartości przy pomocy słowa kluczowego DEFAULT VALUES, jak w poniższym wyrażeniu:

INSERT INTO tmpPublishers DEFAULT VALUES

Wpisywanie danych przy pomocy polecenia SELECT

Wszystkie poprzednie polecenia INSERT wpisywały do tablicy pojedynczy wiersz. Gdy zachodzi potrzeba wpisania większej ilości wierszy należy posiadać źródło, w którym są już zapisane wiersze. Takim źródłem danych jest na ogół inna tablica, złączenie dwóch lub więcej tablic lub widok. W tej formie polecenia INSERT, używane jest podzapytanie określające ile wierszy danych zostanie wpisanych. (Podzapytania zostały omówione w rozdziale 10.). Wyniki podzapytania stają się zbiorem wierszy, który zostanie wpisany przez polecenie INSERT. Ilość kolumn w zbiorze wynikowym podzapytania musi być równa ilości kolumn w tablicy, oraz kolumny muszą mieć zgodne typy danych. W pierwszym przykładzie, zostanie utworzona tablica do śledzenia adresów:

CREATE TABLE tblAddressList

(strName varchar(50) not null,

strAddress varchar(50) not null,

strCity varchar(20) not null,

strState char(2) )

This command completed successfully.

Tablica ta posiada cztery pola znakowe, więc polecenie SELECT wykorzystane do zapełnienia tablicy musi zwrócić cztery kolumny znakowe. Oto przykład:

INSERT INTO tblAddressList

SELECT stor_name, stor_address, city, state

FROM tmpStores

(6 row(s) affected)

Inaczej niż w przypadku podzapytań omawianych wcześniej, podzapytanie używane w poleceniu INSERT nie jest ujęte w nawiasy zwykłe.

Nazwy kolumn używane w podzapytaniu są ignorowane, tablica posiada już nazwy kolumn skojarzone z każdym polem.

Można uruchomić kolejne polecenie INSERT, aby dodać więcej wierszy do tablicy tblAddressList. Potrzeba na przykład dodać nazwy i adresy z tablicy authors. Zamiast pojedynczej nazwy (nazwiska), tablica authors ma osobną kolumnę —imię (au_fname) i nazwisko (au_lname). Tablica address_list oczekuje pojedynczej wartości dla kolumny name, należy więc połączyć imię i nazwisko w jedną wartość. W powstałym wynikowym łańcuchu można użyć przecinka i spacji.

INSERT INTO tblAddressList

SELECT au_lname + ', ' + au_fname, address, city, state

FROM authors

(23 row(s) affected)

Kolejny przykład tworzy tablicę do śledzenia danych (imię i nazwisko) wszystkich wydawców i tytułów wszystkich książek, które zostały opublikowane przez tych wydawców:

CREATE TABLE tblPublisherList

(strPubName varchar(40) NULL,

strTitle varchar(80) NULL)

Aby zapełnić tę tablicę należy złączyć tablice tmpPublishers i tmpTitles. Należy skorzystać ze złączenia zewnętrznego aby zostali uwzględnieni również wydawcy, którzy nie mają obecnie żadnej wydanej książki:

INSERT INTO tblPublisherList

SELECT pub_name, title

FROM tmpPublishers LEFT OUTER JOIN tmpTitles

ON tmpPublishers.pub_id = tmpTitles.pub_id

Korzystanie z procedur składowych do wpisywania danych

SQL Server ma jeszcze jedną opcję do wpisywania danych do tablicy. Jeżeli procedura składowa zwraca pojedynczy wynik i znana jest liczba i typ kolumn, zawartych w zbiorze wynikowym, można wpisać dane (INSERT) do tablicy wywołując procedurę składową i korzystając ze zbioru wyników.

Jeżeli systemowa procedura składowa zwraca więcej niż jeden zbiór wyników, należy się upewnić czy wszystkie wyniki posiadają taką samą liczbę kolumn i zwracają ten sam typ danych w odpowiednich kolumnach.

W rozdziale 15 zostanie omówione tworzenie własnych procedur składowych. Ale już teraz można korzystać z systemowych procedur składowych. Procedura sp_spaceused zwraca informacje na temat wykorzystania przestrzeni pojedynczej tablicy. Można stworzyć tablicę do przetrzymywania wyników uruchomionej procedury i wpisywać (INSERT) wiersze do tej tablicy w określonych odstępach czasu. Wykonując to, można stale monitorować rozrost tablicy.

Warto się przyjrzeć wynikom systemowej procedury składowej sp_spaceused (wyniki mogą się różnić w zależności od systemu):

EXEC sp_spaceused tmpPublishers

Name Rows Reserved Data index_size unused

............... ....... .......... ....... .......... ......

tmpPublishers 11 32KB 2KB 4KB 26KB

Procedura ta zwraca sześć kolumn, wszystkie typu łańcuchów znakowych. Pomimo tego, że druga kolumna wygląda na liczbową, nie jest typu liczbowego. Można określić typ śledząc kod systemowej procedury składowej sp_spaceused (co zostanie omówione w rozdziale 15.) lub próbując utworzyć tablicę z kolumną typu całkowitego. Podczas próby wpisywania wiersza pojawi się komunikat o błędzie.

Następująca tablica powinna przetrzymywać wyniki:

CREATE TABLE tblSpaceUsage

(strTableName varchar(30) not null,

strRows varchar(9),

strReserved varchar(10),

strData varchar(10),

strIndexSize varchar(10),

strUnused varchar(10) )

Aby wpisać dane do tej tablicy, należy uruchomić systemową procedurę składową sp_spaceused:

INSERT INTO tblSpaceUsage

EXEC sp_spaceused 'tmpPublishers'

Jednym z rozszerzeń możliwości wpisywania wierszy z systemowej procedury składowej jest możliwość wpisywania wierszy ze zdalnej (odległej) procedury składowej. Gdy procedura składowa, zwracająca wiersze do tablicy znajduje się na zdalnym (przyłączonym) serwerze, można uruchomić tę zdalną procedurę aby przekopiować wszystkie wiersze ze zdalnego serwera na serwer lokalny. Aby uruchomić zdalną procedurę, należy określić następujące informacje przy jej wywołaniu: nazwę serwera, nazwę bazy danych, nazwę właściciela procedury i nazwę procedury.

Przykładowo, dla SQL Servera o nazwie Wildlife, bazy danych Water i procedury nazwanej Fish, która jest własnością dbo należy uruchomić następujące zapytanie:

INSERT INTO tblLocalTable

EXEC Wildlife.Water.dbo.Fish

Wpisując dane do tablicy z kolumną identity należy wziąć pod uwagę kilka dodatkowych warunków. Zostały one omówione w rozdziale 14., który przedstawia również ograniczenia jakie można napotkać przy próbie wpisywania wartości do tablicy, w której pojawiają się więzy sprawdzające lub więzy integralności referencyjnej.

Usuwanie danych

Przy pomocy polecenia DELETE można usunąć jeden lub więcej wierszy z tablicy:

DELETE [FROM] {table_name | view_name}

[WHERE clause]

Słowo FROM i klauzula WHERE są opcjonalne.

Następujące polecenie DELETE usuwa wszystkie wiersze z tablicy tmpSales:

DELETE tmpSales

(21 row(s) affected)

Aby usunąć jedynie podzbiór wierszy z tablicy, należy użyć klauzuli WHERE, która pozwoli na określenie, które wiersze mają być usunięte. Klauzula WHERE może zawierać dowolne warunki, o których była mowa w rozdziale 10, takie jak operatory relacyjne (<, >, =) i słowa kluczowe IN, LIKE i BETWEEN.

Następujące polecenie DELETE usuwa z tablicy tmpPublishers wszystkie książki, których wydawcą (pub_name) jest New Moon Books:

DELETE tmpPublishers

WHERE pub_name = 'New Moon Books'

Usuwanie danych przy pomocy tablicy przeglądowej (lookup)

Pojedyncze polecenie DELETE może usuwać wiersze jedynie z pojedynczej tablicy. Jednak, SQL Server pozwala na umieszczenie dodatkowej tablicy w poleceniu DELETE, używanej jako tablica przeglądowa (lookup table). Tablice przeglądowe występują często w podzapytaniach. W kolejnym przykładzie, zostaną usunięte wszystkie tytuły publikowane przez New Moon Books. Tablica tmpTitles jest przeznaczona do modyfikacji, ale zawiera jedynie identyfikator wydawcy (ID) a nie nazwę wydawcy. Należy przeszukać tablicę publishers aby znaleźć ID odpowiadający New Moon Books (ten identyfikator ID został już usunięty z tablicy tmpPublishers), który następnie posłuży do określenia wierszy, przeznaczonych do usunięcia z tablicy tmpTitles.

DELETE FROM tblPublisherList

WHERE strPubName =

(SELECT pub_name FROM tmpPublishers

WHERE pub_id = '9956')

(1 row(s) affected)

Podzapytanie dotyczy tablicy tmpPublishers i zwraca pojedynczą wartość pub_name. Wartość ta jest następnie używana do określenia, które wiersze w publisher_list mają być usunięte — czyli, wszystkie wiersze, w których pub_name jest równe zwróconej wartości. Należy zapamiętać, że został zwrócony tylko jeden wiersz ponieważ pub_id jest kluczem podstawowym tablicy publishers. Jeżeli podzapytanie ma zwrócić więcej niż jeden wiersz, należy skorzystać ze słowa kluczowego IN w zamian za znak równości (=).

Wcześniejsze zapytanie może być napisane znacznie efektywniej jako JOIN. Używając polecenia JOIN z poleceniem DELETE należy określić dwa słowa kluczowe FROM jedno do określenia tablicy, z której mają być usunięte rekordy a drugie dla określenia tablicy złączenia. Ten przykład wykonuje takie samo usuwanie jak podane wcześniej:

DELETE tblPublisherList

FROM tblPublisherList pl JOIN tmpPublishers p

On pl.strPubName = p.pub_name

AND p.pub_id = '9956'

Następny przykład używa tablicy przeglądowej, zwracającej więcej niż jedną wartość. Zostaną usunięte wszystkie wiersze z tablicy tmpSales, które oznaczają sprzedaż książek na temat biznesu. Tablica tmpSales przechowuje wartości title_id sprzedanych książek, ale nie przechowuje typu książki. Należy skorzystać z tablicy tmpTitles aby znaleźć, które z identyfikatorów title_id dotyczą książek biznesowych. Ponieważ we wcześniejszej części rozdziału zostały usunięte informacje z tablicy tmpSales, należy ponownie zapełnić tę tablicę. Należy pamiętać, ze na początku niniejszego rozdziału, wszystkie informacje o sprzedaży zostały przekopiowane do innej tablicy. Teraz należy usunąć tablicę tmpSales, a następnie utworzyć ją ponownie i wpisać do niej dane przy pomocy polecenia SELECT INTO:

USE pubs

GO

DROP TABLE tmpSales

GO

SELECT * INTO tmpSales

FROM Sales

GO

Następnie należy usunąć wszystkie wiersze z tablicy tmpSales, które posiadają typ (type) business:

DELETE tmpSales

WHERE title_id IN

(SELECT title_id FROM tmpTitles

WHERE type = 'business')

(5 row(s) affected)

Podzapytanie korzysta z tablicy tmpTitles i zwraca listę wartości title_id. Wartości te są następnie używane do określenia, które wiersze z tablicy tmpSales mają zostać usunięte — czyli wiersze, których title_id jest równe dowolnej ze zwróconych wartości.

Można używać Transact- SQL do pisania poleceń używając DELETE, korzystających z klauzuli FROM, zawierających wiele tablic. Podejście to powoduje, ze DELETE występuje jako operacja złączenia, chociaż wiersze są usuwane tylko z jednej tablicy. Dostarczone funkcje są takie same jak w przypadku używania podzapytań. Druga tablica jest używana jedynie jako tablica przeglądowa.

Następujący przykład pokazuje w jaki sposób polecenie DELETE z klauzulą FROM korzysta z wielu tablic:

DELETE tmpSales

FROM tmpSales s JOIN tmpTitles t

ON s.title_id = t.title_id

WHERE t.type = 'business'

Wybór czy używać podzapytania czy metody złączenia zależy głównie od indywidualnych preferencji użytkownika. Autor niniejszej książki preferuje metodę podzapytań, ponieważ w tym przypadku nie ma wątpliwości, która tablica jest modyfikowana, a która służy jako tablica przeglądowa. Należy również pamiętać, że metoda złączenia nie jest standardem ANSI. Za używaniem metody złączenia przemawia to, że SQL Server wykonuje zwykle złączenia sprawniej niż podzapytania.

Używanie TRUNCATE TABLE

We wcześniejszej części rozdziału pokazano polecenie DELETE z klauzulą WHERE, które usuwało z tablicy wszystkie wiersze. Jeżeli z tablicy mają być usunięte wszystkie dane, ale struktura tablicy ma pozostać nietknięta, można wybrać alternatywną metodę:

TRUNCATE TABLE tmpSales

Przeciwnie niż polecenie DELETE, polecenie to nie zwraca komunikatu o ilości przetwarzanych wierszy. Poniżej przedstawiono niektóre różnice pomiędzy DELETE bez klauzuli WHERE a poleceniem TRUNCATE TABLE:

• DELETE rejestruje każdy wiersz, który jest usuwany; TRUNCATE TABLE zapisuje w dzienniku transakcji jedynie przesunięcia strony i zakresu.

• DELETE zarządza indeksami poprzez usuwanie jednego wskaźnika w danym czasie i rejestrację każdego dopasowania indeksu — TRUNCATE TABLE zmniejsza indeksy w pojedynczym kroku i ponownie zapisuje przesunięcia stron w dzienniku transakcji.

• TRUNCATE TABLE może być wykonywane jedynie przez właściciela tablicy, członka roli bazy danych db_owner lub roli serwera sysadmin; DELETE może być wykonywane przez dowolnego użytkownika, który otrzymał odpowiednie uprawnienia do tej tablicy.

• Procedura wyzwalana DELETE nie jest uruchamiana gdy tablica jest zmniejszana ale jest uruchamiana gdy wiersze są usuwane (więcej informacji na temat procedur wyzwalanych dostarczy rozdział 15).

• TRUNCATE TABLE resetuje wszelkie wartości identity do wartości początkowych (seed); DELETE nie ma wpływu na kolejną wartość identity, która zostanie użyta.

Aktualizacja danych

Trzecim omówionym w tym rozdziale poleceniem modyfikującym dane jest polecenie UPDATE. Polecenie to pozwala zmienić wartości w kolumnach w istniejącym wierszu.

Przed omówieniem kolejnych zagadnień należy odświeżyć tablice w bazie, aby dane były aktualne. W celu odświeżenia tablic tmpSales, tmpPublishers, tmpStores i tmpTitles, należy uruchomić następujące zapytanie:

USE pubs

GO

DROP TABLE tmpSales

DROP TABLE tmpPublishers

DROP TABLE tmpStores

DROP TABLE tmpTitles

GO

SELECT * INTO tmpPublishers FROM publishers

SELECT * INTO tmpSales FROM sales

SELECT * INTO tmpStores FROM stores

SELECT * INTO tmpTitles FROM titles

GO

Polecenie UPDATE

Polecenie UPDATE posiada następującą składnię:

UPDATE

{table_name | view_name}

SET

column_name1 = {expression1 | NULL | (select_statement)}

[, column_name2= ...]

[WHERE search_conditions]

Klauzula SET określa kolumny, które mają być aktualizowane. Podobnie jak w polecenie DELETE, klauzula WHERE jest opcjonalna.

Polecenie UPDATE ustawia kolumnę ytd_sales w tablicy tmpTitles na wartość 0 dla każdego wiersza. Można życzyć sobie aktualizacji tej kolumny na początku każdego roku.

UPDATE tmpTitles

SET ytd_sales = 0

(18 row(s) affected)

Bez klauzuli WHERE polecenie to zmienia wartość kolumny ydt_sales na wartość 0 w każdym wierszu w tablicy.

Następujący przykład uaktualnia kolumnę city dla wydawcy Algodata Infosystems:

UPDATE tmpPublishers

SET city = 'El Cerrito'

WHERE pub_name = 'Algodata Infosystems'

(1 row(s) affected)

Polecenie UPDATE może tworzyć nowe wartości w kolumnie zależnie od oryginalnych wartości. Poniższy przykład zmniejsza cenę wszystkich książek na temat psychologii o 10% w stosunku do ceny bieżącej:

UPDATE tmpTitles

SET price = price * 0.90

WHERE type = 'psychology'

(5 row(s) affected)

Polecenie UPDATE może zmienić więcej niż jedną kolumnę. Słowo SET pojawia się tylko raz, a różne kolumny, które mają być zmienione zostają oddzielone od siebie przecinkami. Następujące polecenie UPDATE zwiększa cenę wszystkich popularnych książek na temat komputerów o 20% i dopisuje łańcuch znaków (price increase)do pola notatek w modyfikowanych wierszach:

UPDATE tmpTitles

SET price = price * 1.2, notes = notes + ' (price increase)'

WHERE type = 'popular_comp'

(3 row(s) affected)

Wykonywanie polecenia UPDATE korzystając z tablicy przeglądowej

Pojedyncze polecenie UPDATE może zmieniać wiersze jedynie w pojedynczej tablicy. Jednak, SQL Server pozwala na umieszczanie w poleceniu UPDATE drugiej tablicy używanej jako tablica przeglądowa (lookup). Zwykle, tablica przeglądowa pojawia się w podzapytaniu, które może występować w klauzuli WHERE lub w klauzuli SET polecenia UPDATE. W następnym przykładzie, zostanie zmieniony wydawca wszystkich książek o biznesie na New Moon Book:

UPDATE tmpTitles

SET pub_id =

(SELECT pub_id FROM tmpPublishers

WHERE pub_name = 'New Moon Books')

WHERE type = 'business'

(4 row(s) affected)

Nazwa wydawcy pojawia się jedynie w tablicy tmpPublishers, ale tablica tmpTitles musi być modyfikowana. Podzapytanie dotyczy tablicy tmpPublishers i zwraca ID wydawcy dla New Moon Books. Wartość ta jest używana jako nowa wartość w kolumnie pub_id tablicy tmpTitles.

Tak jak w przypadku polecenia DELETE, można używać Transact-SQL do zapisywanie poleceń UPDATE przy pomocy klauzuli FROM zawierającej wiele tablic. Podejście to zapewnia, że polecenie UPDATE występuje jako operacja złączenia, chociaż tylko w jednej tablicy są modyfikowane wiersze. Dostępne funkcje są takie same jak używane w podzapytaniach: druga tablica jest używana jedynie jako tablica przeglądowa.

Następujący przykład pokazuje jak można przepisać poprzednie polecenie UPDATE przy pomocy wielu tablic w klauzuli FROM:

UPDATE tmpTitles

UPDATE tmpTitles

SET tmpTitles.pub_id = p.pub_id

FROM tmpTitles t JOIN tmpPublishers p

ON t.pub_id = p.pub_id

WHERE p.pub_name = 'New Moon Books'

AND type = 'business'

Wybór czy używać metody podzapytania czy metody złączenia zależy głównie od indywidualnych preferencji użytkownika. Podobnie jak w przypadku DELETE, metoda podzapytań wydaje się bardziej przejrzysta, jeżeli chodzi o określenie, która tablica jest modyfikowana, jaka będzie nowa wartość pub_id, i które wiersze uległy zmianie. Należy również pamiętać, że metoda złączenia nie jest standardem ANSI.

Niektóre polecenia UPDATE są bardziej skomplikowane do napisania przy pomocy metody złączenia. Jednym z takich przypadków jest zapytanie, gdzie polecenie UPDATE używa podzapytań zarówno w klauzuli SET jak i w klauzuli WHERE.

Przykład ten zmienia wydawcę wszystkich książek psychologicznych publikowanych przez New Moon Books na Binnet & Hardley:

UPDATE tmpTitles

SET pub_id =

(SELECT pub_id FROM tmpPublishers

WHERE pub_name = 'Binnet & Hardley')

WHERE type = 'psychology' AND pub_id =

(SELECT pub_id FROM tmpPublishers

WHERE pub_name = 'New Moon Books')

(4 row(s) affected)

Ponownie, nazwa wydawcy pojawia się jedynie w tablicy tmpPublishers, ale modyfikowana ma być tablica tmpTitles. Pierwsze podzapytanie sięga do tablicy tmpPublishers i zwraca ID wydawcy dla Binnet & Hardley. Wartość pub_id jest używana jako nowa wartość w kolumnie pub_id tablicy tmpTitles. Drugie podzapytanie dotyczy również tablicy tmpPublishers i zwraca pub_id dla New Moon Books. Ten pub_id jest używany do określenia, które wiersze tablicy tmpTitles muszą zostać uaktualnione.

To polecenie UPDATE byłoby znacznie trudniejsze do napisania przy pomocy metody złączenia ponieważ tablica tmpPublishers powinna się pojawiać dwukrotnie: raz aby określić nową wartość pub_id i raz aby określić pub_id dla zmienianych wierszy. Jest to kolejna przyczyna, dla której warto stosować metodę podzapytań zamiast złączenia.

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

14 C:\moj dysk\studia\pracamag\materiały\helion\ksiazki\SQL Server 2000 dla kazdego\r11-05.doc

Nowa wskazówka.

---