Оновлення даних

Скажу без перебільшення, що інструкція UPDATE є виключно потужним інструментом Те, що раніше займало десятки програмних рядків і кілька вкладених циклів, тепер можна реалізувати за допомогою всього однієї інструкції SQL не можна назвати повною мірою справжнім командним мовою – він є декларативним Код SQL всього лише ставить завдання оптимізаторові запитів Після цього оптимізатор розробляє найкращий план отримання відповіді на поставлене запитання і виконує сформовану задачу При цьому він оцінює, скільки використовувати таблиць і в якому порядку, як їх обєднувати і які індекси використовувати Він враховує статистику заповнення таблиць та їх розміри продуктивність процесора і дискової системи, ємність памяті Тільки на основі всієї цієї інформації він здатний сформувати план виконання запиту Написання програм, оновлюють послідовно рядки, ніколи не позволілоби домогтися такого рівня оптимізації

Оновлення однієї таблиці

Інструкція UPDATE мови SQL гранично проста Вона здатна змінити значення всього одного осередку, а також всіх стовпців всіх рядків таблиці Однак пропозиція FROM дозволяє таблиці стати частиною складеного джерела даних і використовувати всю силу інструкції SELECT

Ось як працює інструкція UPDATE:

UPDATE dbo таблиця

SET стовпець = значення, / вираз / стовпчик, стовпчик = значення ..

[FROM істочнікі_данних]

[WHERE умови]

Інструкція UPDATE може змінити значення безлічі рядків, але тільки однієї таблиці Ключове слово SET використовується для перетворення даних в нові значення Ці нові значення можуть бути константами, змінними, виразами і навіть стовпцями іншого джерела даних, наявного в реченні FROM інструкції UPDATE

Додаткова Повний список можливостей виразів див в розділі 8

інформація

Пропозиція WHERE життєво важливо для будь-якої інструкції UPDATE, оскільки в його відсутність оновлюється вся таблиця Якщо пропозиція WHERE присутній, то оновлюються тільки ті рядки, які їм не відфільтровані Перевіряйте й ще раз перевіряйте пропозицію WHERE Не вважайте мене занудним, але я ще раз повторю: Сім разів відміряй, один відріж.

У наступному прикладі інструкція UPDATE виконує типову операцію з реального життя – вона змінює значення одного стовпця одного рядка Найкраще відфільтрувати один рядок за допомогою первинного ключа:

USE СНА2

UPDATE dboGuide

SET Qualifications = Spelunking, Cave Diving,

First Aid, Navigation

Where GuidelD = 6

Перевіримо результати оновлення:

SELECT GuidelD, LastName, Qualifications FROM dboGuide WHERE GuidelD = 6

Буде отримано наступний результат:

GuidelD         LastName Qualifications

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Bistier Spelunking, Cave Diving,

First Aid, Navigation

Виконання глобального пошуку і заміни

Очищення бази даних від сміття представляє собою типову задачу її адміністратора На щастя, SQL містить функцію replace (), яка в комбінації з інструкцією UPDATE може допомогти в глобальному пошуку і заміні

У наступному прикладі, який посилається на навчальну базу даних Family, ми будемо шукати входження подвійної літери 1 (тобто І) в прізвища та замінювати їх на qua:

Use Family Update Person

Set LastName = Replace(Lastname, 11, qua)

Тепер перевіримо результат виконання інструкції, витягуючи прізвища (результуючий список скорочений):

Select lastname from Person

lastname

Haquaoway

Haquaoway

Miquaer

Miquaer

Haquaoway

Посилання на безліч таблиць при оновленні

даних

Більш потужної функцією інструкції UPDATE є присвоєння колонки результату вираження, яке посилається на той же або інші стовпці або навіть на інші таблиці

Хоча вираження доступні і при роботі з однією таблицею, найчастіше в них доводиться посилатися на дані, що знаходяться поза оновлюваної таблиці Необовязкове пропозицію FROM дозволяє створювати обєднання між оновлюваної таблицею та іншими джерелами даних Оновлюватися може тільки одна таблиця, проте будучи обєднаної з іншими, вона може отримувати дані з їх стовпців і використовувати у виразах оновлення

Одним із способів візуалізації обєднань, створюваних в реченні FROM, є злиття всіх таблиць в новий єдиний надширокий результуючий набір даних Після цього частина, що залишилася інструкції SQL працює вже з цим новим безліччю Незважаючи на те що саме так і працює пропозицію FROM, реальна інструкція UPDATE працює не з цим безліччю, а тільки з таблицею, зазначеної відразу після ключового слова UPDATE

Синтаксис інструкції update from входить в розширення Т-SQL і не міститься в стандарті ANSI SQL-92 Якщо в майбутньому планується перенесення бази даних на іншу платформу, то для поновлення коректних рядків використовуйте підзапити

Розглянемо приклад з реального життя Припустимо, що всі співробітники невдовзі мають отримати підвищення зарплати, розмір якої залежить від підрозділу, часу знаходження в поточній посаді, виробничих результатів і терміну роботи в компанії (згоден, в реальному житті таке навряд чи можливо) Якщо відсоток для кожного відділу зберігається в таблиці Department, то всього одна інструкція UPDATE дозволяє обєднати її з таблицею Employee і врахувати коефіцієнт розміру премії Припустимо, що формула нарахувань наступна:

2 + (((стаж_в_компаніі * 0,1) + (месяцев_на_должності * 0, 02)

+ ((Рейтинг_производительности * 0, 5) якщо більше 2))

* коеффіціент_отдела)

У пропонованому нижче сценарії створюється кілька таблиць, які заповнюються даними, після цього тестується формула і в результаті виконується оновлення

USE Tempdb

CREATE TABLE dboDept (

DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptName VARCHAR(50) NOT NULL,

RaiseFactor NUMERIC(4,2)

)

ON [Primary]

go

Create TABLE dboEmployee (

EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptID INT FOREIGN KEY REFERENCES Dept,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Salary INT,

PerformanceRating NUMERIC(4,2),

DateHire DATETIME,

DatePosition DATETIME )

ON [Primary]

go

– Створення даних прикладу

INSERT dboDept VALUES (Engineering , 12)

INSERT dboDept VALUES (Sales,8)

INSERT dboDept VALUES ( IT ,25)

INSERT dboDept VALUES (Manufacturing,10) go

INSERT dboEmployee

VALUES( 1,Smith, Sam,54000, 20, 1/1/97, 1/4/2001 )

INSERT dboEmployee

VALUES( 1,Nelson,Slim,78000,15, 1/9/88, 1/1/2000 )

INSERT dboEmployee

VALUES( 2,Ball, Sally,45000,35, 1/8/99, 1/1/2001 )

INSERT dboEmployee

VALUES( 2, Kelly, Jeff,85000,24, 1/10/83, 1/9/1998 )

INSERT dboEmployee

VALUES( 3, Guelzow1, Jo,120000,40, 1/7/95, 1/6/2001 )

INSERT dboEmployee

VALUES( 3, Anderson, Missy,95000,18, 1/2/99, 1/9/97 )

INSERT dboEmployee

VALUES( 4,Reagan, Frank,75000,29, 1/4/00, 1/4/2000 )

INSERT dboEmployee

VALUES( 4, Adams, Hank,34000,32, 1/9/98, 1/9/1998 )

Припускаючи, що датою підвищення зарплати є 1/5/2002, наступний запит протестує дані прикладу:

SELECT LastName, Salary,

DateDiff (уу, DateHire, 1 / 5/2002 ) as YearsCo,

DateDiff(mm, DatePosition, 1/5/2002) as MonthPosition,

CASE

WHEN EmployeePerformanceRating &gt= 2 THEN EmployeePerformanceRating ELSE 0 END as Performance,

DeptRaiseFactor AS Dept

FROM dboEmployее JOIN dboDept

ON EmployeeDeptID = DeptDeptID

Повинен бути отриманий наступний результат:

LastName Salary YearsCo MonthPosition Performance Dept

Smith            54000      5                        13                   200   120

Nelson          78000      14                      28                    00   120

Ball              45000      3                        16                  350   80

Kelly             85000      19                      44                   240   80

Guelzow                      120000 7             11                  400   250

Anderson       95000      3                        56                    00   250

Reagan          75000      2                        25                   290   100

Adams           34000      4                        44                  320   100

На підставі заповнених даних виконаємо запит, який обчислює коефіцієнт підвищення зарплати:

SELECT LastName,

(2 + (((DateDiff (уу, DateHire, 1 / 5/2002 ) * 1)

+ (DateDiff(mm, DatePosition, 1/5/2002) * 02)

+ (CASE

WHEN EmployeePerformanceRating &gt= 2

THEN EmployeePerformanceRating ELSE 0 END * 5 ))

*&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp DeptRaiseFactor))/100 as EmpRaise FROM dboEmployee

JOIN dboDept

ON EmployeeDeptID = DeptDeptID

Буде отримано наступний результат:

LastName       EmpRaise

Smith    041120000

Nelson  043520000

Ball      038960000

Kelly     051840000

Guelzow         093000000

Anderson       055500000

Reagan  041500000

Adams  048800000

Отже, дані внесені, і формули перевірені Тепер прийшов час виконати інструкцію UPDATE і скоригувати зарплату працівників:

UPDATE Employee SET Salary = Salary * (1 +

(2 + (((DateDiff(yy, DateHire, 11/5/2002) * 1)

+ (DateDiff(mm, DatePosition, 1/5/2002) * 02)

+ (CASE

WHEN EmployeePerformanceRating &gt= 2 THEN EmployeePerformanceRating ELSE 0 END * 5 ))

*&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp DeptRaiseFactor))/100 )

FROM dboEmployee

JOIN dboDept

ON EmployeeDeptID = DeptDeptID

Наступна інструкція SELECT дозволить нам побачити плоди своєї праці:

SELECT FirstName, LastName, Salary FROM Employee

А ось і сам результат:

FirstName LastName  Salary

Sam  Smith               56220

Slim Nelson              81394

Sally Ball                  46753

Jeff  Kelly                89406

Dave Guelzow           131160

Missy                       Anderson           100272

Frank                       Reagan   78112

Hank                        Adams    35659

На завершення нашого прикладу очистимо створені навчальні таблиці:

DROP TABLE dboEmployee DROP TABLE dboDept

У наведеному вище прикладі були зведені воєдино операції, описані в декількох попередніх розділах, у тому числі створення і видалення таблиць, вираження CASE, обєднання, скалярні функції роботи з датами, не кажучи вже про операції вставки і оновлення, описаних у цій главі Приклад був досить довгим, оскільки продемонстрував

більше, ніж одну інструкцію UPDATE У ньому був показаний типовий процес проектування складних оновлень, який включає декілька етапів

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Перевірка доступних даних Перша інструкція SELECT обєднала таблиці Employee і Dept і вивела список всіх стовпців, необхідних формулою

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Тестування формули Друга інструкція SELECT базувалася на першому і застосувала формулу до потрібних стовпцях На підставі отриманих даних можна вручну перевірити результати по обраним позиціях і таким чином перевірити формулу

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Виконання оновлення Як тільки формула створена і перевірена, її можна підставити в інструкцію UPDATE, яку, в свою чергу, слід виконати

Р1нструкцію UPDATE можна не назвати потужною Я замінив виключно складні набори даних і вкладені цикли, що виконуються повільно і до того ж схильні до помилок, всього однією інструкцією UPDATE з грамотно створеним обєднанням У результаті час виконання скоротилося з декількох хвилин до лічених секунд У той же час я анітрохи не применшую гідності підходу до оновлення за допомогою наборів, а не рядків даних

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*