Эх, не могу я так, все-таки давайте посмотрим, как это работает наглядно.

Для этого опять вспомним DDL и временно создадим поля Position и Department в таблице Employees:

ALTER TABLE Employees ADD Position nvarchar(30),Department nvarchar(30)

 

Зальем в них данные, предварительно посмотрев при помощи SELECT, что получится:

SELECT

e.ID,

e.Position,

p.Name NewPosition,

e.Department,

d.Name NewDepartment

FROM Employees e

LEFT JOIN Positions p ON p.ID=e.PositionID

LEFT JOIN Departments d ON d.ID=e.DepartmentID

 

Теперь перепишем и выполним обновление:

UPDATE e

SET

e.Position=p.Name,

e.Department=d.Name

FROM Employees e

LEFT JOIN Positions p ON p.ID=e.PositionID

LEFT JOIN Departments d ON d.ID=e.DepartmentID

 

Посмотрите, что получилось (должны были появиться значения в 2-х полях – Position и Department, находящиеся в конце таблицы):

SELECT *

FROM Employees

 

Теперь и этот запрос:

UPDATE e

SET

PositionID=(SELECT ID FROM Positions WHERE Name=e.Position),

DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department)

FROM Employees e

 

И этот:

UPDATE e

SET

PositionID=p.ID,

DepartmentID=d.ID

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department

 

Отработают успешно.

Не забудьте только предварительно посмотреть (это очень полезная привычка):

SELECT

e.ID,

e.PositionID,e.DepartmentID, -- старые значения

e.Position,e.Department,

p.ID,d.ID, -- новые значения

p.Name,d.Name

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department

 

И конечно же можете использовать здесь условие WHERE:

UPDATE e

SET

PositionID=p.ID,

DepartmentID=d.ID

FROM Employees e

LEFT JOIN Positions p ON p.Name=e.Position

LEFT JOIN Departments d ON d.Name=e.Department

WHERE d.ID=3 -- обновить только данные по ИТ-отделу

 

Все, убедились, что все работает. Если хотите, то можете снова удалить поля Position и Department.

Вторую форму можно так же использовать с подзапросом:

UPDATE e

SET

HireDate='20131101',

MiddleName=N'Иванович'

FROM (SELECT MiddleName,HireDate FROM Employees WHERE ID=1000) e

 

В данном случае подзапрос должен возвращать в явном виде строки таблицы Employees, которые будут обновлены. В подзапросе нельзя использовать группировки или предложения DISTINCT, т.к. в этом случае мы не получим явных строк таблицы Employees. И соответственно все обновляемые поля должны содержаться в предложении SELECT, если конечно вы не указали «SELECT *».

Так же с UPDATE вы можете использовать CTE-выражения. Для примера перенесем наш подзапрос в блок WITH:

WITH cteEmp AS(

SELECT MiddleName,HireDate FROM Employees WHERE ID=1000

)

UPDATE cteEmp

SET

HireDate='20131101',

MiddleName=N'Иванович'

 

Идем дальше.

DELETE – удаление данных

Принцип работы DELETE похож на принцип работы UPDATE, и так же в MS SQL можно использовать 2 формы:

1. DELETE таблица WHERE условие_выборки – удаление строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут удалены все строки. Это можно сказать классическая форма оператора DELETE (только в некоторых СУБД нужно писать DELETE FROM таблица WHERE условие_выборки).

2. DELETE псевдоним FROM … – удаление данных таблицы участвующей в предложения FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.

 

Для примера при помощи первого варианта:

-- удалим неиспользуемые должности Логист и Кладовщик

DELETE Positions WHERE ID IN(6,7)

 

При помощи второго варианта удалим остальные неиспользуемые должности. В целях демонстрации запрос намеренно излишне усложнен. Сначала посмотрим, что именно удалиться (всегда старайтесь делать проверку, а то ненароком можно удалить лишнее, а то и всю информацию из таблицы):

SELECT pos.*

FROM

(

SELECT DISTINCT PositionID

FROM Employees

) emp

RIGHT JOIN Positions pos ON pos.ID=emp.PositionID

WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees

 

Убедились, что все нормально. Переписываем запрос на DELETE:

DELETE pos -- удалить из этой таблицы

FROM

(

SELECT DISTINCT PositionID

FROM Employees

) emp

RIGHT JOIN Positions pos ON pos.ID=emp.PositionID

WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees

 

В качестве таблицы Positions может выступать и подзапрос, главное, чтобы он однозначно возвращал строки, которые будут удаляться. Давайте добавим для демонстрации в таблицу Positions мусора:

INSERT Positions(Name) VALUES('Test 1'),('Test 2')

 

Теперь для демонстрации используем вместо таблицы Positions, подзапрос, в котором отбираются только определенные строки из таблицы Positions:

DELETE pos -- удалить из этой таблицы

FROM

(

SELECT DISTINCT PositionID

FROM Employees

) emp

RIGHT JOIN

(

SELECT ID

FROM Positions

WHERE ID>4 -- отбираем должности по условию

) pos

ON pos.ID=emp.PositionID

WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees

 

Так же мы можем использовать CTE выражения (подзапросы, оформленные в блоке WITH). Давайте снова добавим для демонстрации в таблицу Positions мусора:

INSERT Positions(Name) VALUES('Test 1'),('Test 2')

 

И посмотрим на тот же запрос с CTE-выражением:

WITH ctePositionc AS(

SELECT ID

FROM Positions

WHERE ID>4 -- отбираем должности по условию

)

DELETE pos -- удалить из этой таблицы

FROM

(

SELECT DISTINCT PositionID

FROM Employees

) emp

RIGHT JOIN ctePositionc pos ON pos.ID=emp.PositionID

WHERE emp.PositionID IS NULL -- нет среди должностей указанных в Employees

 

Заключение по INSERT, UPDATE и DELETE

Вот по сути и все, что я хотел рассказать вам про основные операторы модификации данных – INSERT, UPDATE и DELETE.

Я считаю, что данные операторы очень легко понять интуитивно, когда умеешь пользоваться конструкциями оператора SELECT. Поэтому рассказ о операторе SELECT растянулся на 3 части, а рассказ о операторах модификации был написан в такой беглой форме.

И как вы увидели, с операторами модификации тоже полет фантазии не ограничен. Но все же старайтесь писать, как можно проще и понятней, обязательно предварительно проверяя, какие записи будут обработаны при помощи SELECT, т.к. обычно модификация данных, это очень большая ответственность.

В дополнение скажу, что в диалекте MS SQL cо всеми операциями модификации можно использовать предложение TOP (INSERT TOP …, UPDATE TOP …, DELETE TOP …), но мне пока ни разу не приходилось прибегать к такой форме, т.к. здесь непонятно какие именно TOP записей будут обработаны.

Если уж нужно обработать TOP записей, то я, наверное, лучше воспользуюсь указанием опции TOP в подзапросе и применю в нем нужным мне образом ORDER BY, чтобы явно знать какие именно TOP записей будут обработаны. Для примера снова добавим мусора:

INSERT Positions(Name) VALUES('Test 1'),('Test 2')

 

И удалим 2 последние записи:

DELETE emp

FROM

(

SELECT TOP 2 * -- 2. берем только 2 верхние записи

FROM Positions

ORDER BY ID DESC -- 1. сортируем по убыванию

) emp

 

Я здесь привожу примеры больше в целях демонстрации возможностей языка SQL. В реальных запросах старайтесь выражать свои намерения очень точно, дабы выполнение вашего запроса не привело к порче данных. Еще раз скажу – будьте очень внимательны, и не ленитесь делать предварительные проверки.

SELECT … INTO … – сохранить результат запроса в новой таблице

Данная конструкция позволяет сохранить результат выборки в новой таблице. Она представляет из себя что-то промежуточное между DDL и DML.

Типы колонок созданной таблицы будут определены на основании типов колонок набора, полученного запросом SELECT. Если в выборке присутствуют результаты выражений, то им должны быть заданы псевдонимы, которые будут служить в роли имен колонок.

Давайте отберем следующие данные и сохраним их в таблице EmployeesBonusTarget (перед FROM просто пишем INTO и указываем имя новой таблицы):

SELECT

bonus.EmployeeID,

bonus.BonusDate,

bonus.BonusAmount-bonus.BonusAmount BonusAmount, -- обнулим значения

bonus.BonusTypeID,

bonus.BonusPercent,

bonus.Note

INTO EmployeesBonusTarget -- сохраним результат в новой таблице EmployeesBonusTarget

FROM EmployeesBonus bonus

JOIN Employees emp ON bonus.EmployeeID=emp.ID

WHERE emp.DepartmentID=3

 

Можете обновить список таблиц в инспекторе объектов и увидеть новую таблицу EmployeesBonusTarget:

На самом деле я специально создал таблицу EmployeesBonusTarget, я ее буду использовать для демонстрации оператора MERGE.

Еще пара слов про конструкцию SELECT … INTO …

Данную конструкцию иногда удобно применять при формировании очень сложных отчетов, которые требуют выборки из множества таблиц. В этом случае данные обычно сохраняют во временных таблицах (#). Т.е. предварительно при помощи запросов, мы сбрасываем данные во временные таблицы, а затем используем эти временные таблицы в других запросах, которые формируют окончательный результат:

SELECT

ID,

CONCAT(LastName,' ',FirstName,' ',MiddleName) FullName, -- используем псевдоним FullName

Salary,

BonusPercent,

Salary/100*ISNULL(BonusPercent,0) Bonus -- используем псевдоним Bonus

INTO #EmployeesBonus -- сохранить результат во временной таблице

FROM Employees

 

SELECT …

FROM #EmployeesBonus b

JOIN …

 

Иногда данную конструкцию удобно использовать, чтобы сделать полную копию всех данных текущей таблицы:

SELECT *

INTO EmployeesBackup

FROM Employees

 

Это можно использовать, например, для подстраховки, перед тем как вносить серьезные изменения в структуру таблицы Employees. Вы можете сохранить копию либо всех данных таблицы, либо только тех данных, которых коснется модификация. Т.е. если что-то пойдет не так, вы сможете восстановить данные таблицы Employees с этой копии. В таких случаях конечно хорошо сделать предварительный бэкап БД на текущий момент, но это бывает не всегда возможно из-за огромных объемов, срочности и т.п.

Чтобы не засорять основную базу, можно создать новую БД и сделать копию таблицы туда:

CREATE DATABASE TestTemp

GO

 

SELECT *

INTO TestTemp.dbo.EmployeesBackup -- используем префикс ИмяБаза.Схема.

FROM Employees

 

Для того чтобы увидеть новую БД TestTemp, соответственно, обновите в инспекторе объектов список баз данных, в ней и уже можете найти данную таблицу.

На заметку.
В БД Oracle так же есть конструкция для сохранения результата запроса в новую таблицу, выглядит она следующим образом:

CREATE TABLE EMPLOYEES_BACK -- сохранить результат в новой таблице с именем EMPLOYEES_BACK

AS

SELECT *

FROM EMPLOYEES

 

 

MERGE – слияние данных

Данный оператор хорошо подходит для синхронизации данных 2-х таблиц. Такая задача может понадобится при интеграции разных систем, когда данные передаются порциями из одной системы в другую.

В нашем случае, допустим, что стоит задача синхронизации таблицы EmployeesBonusTarget с таблицей EmployeesBonus.

Давайте добавим в таблицу EmployeesBonusTarget какого-нибудь мусора:

INSERT EmployeesBonusTarget(EmployeeID,BonusDate,BonusAmount,BonusTypeID,Note)VALUES

(9999,'20150101',9999.99,0,N'это мусор'),

(9999,'20150201',9999.99,0,N'это мусор'),

(9999,'20150301',9999.99,0,N'это мусор'),

(9999,'20150401',9999.99,0,N'это мусор'),

(9999,'20150501',9999.99,0,N'это мусор'),

(9999,'20150601',9999.99,0,N'это мусор')

 

Теперь при помощи оператора MERGE добьемся того, чтобы данные в таблице EmployeesBonusTarget стали такими же, как и в EmployeesBonus, т.е. сделаем синхронизацию данных.

Синхронизацию мы будем осуществлять на основании сопоставления данных входящих в первичный ключ таблицы EmployeesBonus (EmployeeID, BonusDate, BonusTypeID):

1.Если для строки таблицы EmployeesBonusTarget соответствия по ключу не нашлось, то нужно сделать удаление таких строк из EmployeesBonusTarget

2.Если соответствие нашлось, то нужно обновить строки EmployeesBonusTarget данными соответствующей строки из EmployeesBonus

3.Если строка есть в EmployeesBonus, но ее нет в EmployeesBonusTarget, то ее нужно добавить в EmployeesBonusTarget

Сделаем реализацию всей этой логики при помощи инструкции MERGE:

MERGE EmployeesBonusTarget trg -- таблица приемник

USING EmployeesBonus src -- таблица источник

ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния

 

-- 1. Строка есть в trg но нет сопоставления со строкой из src

WHEN NOT MATCHED BY SOURCE THEN

DELETE

 

-- 2. Есть сопоставление строки trg со строкой из источника src

WHEN MATCHED THEN

UPDATE SET

trg.BonusAmount=src.BonusAmount,

trg.BonusPercent=src.BonusPercent,

trg.Note=src.Note

 

-- 3. Строка не найдена в trg, но есть в src

WHEN NOT MATCHED BY TARGET THEN -- предложение BY TARGET можно отпускать, т.е. NOT MATCHED = NOT MATCHED BY TARGET

INSERT(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent,Note)

VALUES(src.EmployeeID,src.BonusDate,src.BonusAmount,src.BonusTypeID,src.BonusPercent,src.Note);