Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть пятая

Предыдущие части

• Часть первая — habrahabr.ru/post/255361 ^[1]
• Часть вторая — habrahabr.ru/post/255523 ^[2]
• Часть третья — habrahabr.ru/post/255825 ^[3]
• Часть четвертая — habrahabr.ru/post/256045 ^[4]

В данной части мы рассмотрим

Здесь мы в общих чертах рассмотрим работу с операторами модификации данных:

• INSERT – вставка новых данных
• UPDATE – обновление данных
• DELETE – удаление данных
• SELECT … INTO … – сохранить результат запроса в новой таблице
• MERGE – слияние данных
• TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы

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

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

Т.к. прямая модификация информации в РБД требует от человека большой ответственности, а также потому что пользователи обычно модифицируют информацию БД посредством разных АРМ, и не имеют полного доступа к БД, то данная часть больше посвящается начинающим ИТ-специалистам, и я буду здесь очень краток. Но конечно, если вы смогли освоить оператор SELECT, то думаю, и операторы модификации вам будут под силу, т.к. после оператора SELECT здесь нет ничего сверхсложного, и по большей части должно восприниматься на интуитивном уровне. Но порой сложность представляют не сами операторы модификации, а то что они должны выполняться группами, в рамках одной транзакции, т.е. когда дополнительно нужно учитывать целостность данных. В любом случае можете почитать и попытаться проделать примеры в ознакомительных целях, к тому же в итоге вы сможете получить более детальную базу, на которой можно будет отработать те или иные конструкции оператора SELECT.

Проведем изменения в структуре нашей БД

Давайте проведем небольшое обновление структуры и данных таблицы Employees:

-- информацию по ЗП решено хранить до 2-х знаков после запятой
ALTER TABLE Employees ALTER COLUMN Salary numeric(20,2)
-- информацию по процентам решено хранить только в целых числах
ALTER TABLE Employees ALTER COLUMN BonusPercent tinyint

А также для демонстрационных целей расширим схему нашей БД, а за одно повторим DDL. Назначения таблиц и полей указаны в комментариях:

-- история изменений ЗП у сотрудников
CREATE TABLE EmployeesSalaryHistory(
  EmployeeID int NOT NULL, -- ссылка на ID сотрудника
  DateFrom date NOT NULL,  -- с какой даты
  DateTo date,             -- по какую дату. Содержит NULL если это последняя установленная ЗП.
  Salary numeric(20,2) NOT NULL, -- сумма ЗП за этот период
CONSTRAINT PK_EmployeesSalaryHistory PRIMARY KEY(EmployeeID,DateFrom),
CONSTRAINT FK_EmployeesSalaryHistory_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID)
)
GO

-- таблица для хранения истории начислений по ЗП
CREATE TABLE EmployeesSalary(
  EmployeeID int NOT NULL,
  SalaryDate date NOT NULL, -- дата начисления
  SalaryAmount numeric(20,2) NOT NULL, -- сумма начисления
  Note nvarchar(50), -- примечание
-- здесь сумма ЗП может фиксироваться по человеку 1 раз в день
CONSTRAINT PK_EmployeesSalary PRIMARY KEY(EmployeeID,SalaryDate),
-- связь с таблицей Employees
CONSTRAINT FK_EmployeesSalary_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID)
)
GO

-- справочник по типам бонусов
CREATE TABLE BonusTypes(
  ID int IDENTITY(1,1) NOT NULL,
  Name nvarchar(30) NOT NULL,
CONSTRAINT PK_BonusTypes PRIMARY KEY(ID)
)
GO

-- таблица для хранения истории начислений бонусов
CREATE TABLE EmployeesBonus(
  EmployeeID int NOT NULL,
  BonusDate date NOT NULL, -- дата начисления
  BonusAmount numeric(20,2) NOT NULL, -- сумма начисления
  BonusTypeID int NOT NULL,
  BonusPercent tinyint,
  Note nvarchar(50), -- примечание
-- бонус одного типа может фиксироваться по человеку 1 раз в день
CONSTRAINT PK_EmployeesBonus PRIMARY KEY(EmployeeID,BonusDate,BonusTypeID),
-- связь с таблицей Employees и BonusTypes
CONSTRAINT FK_EmployeesBonus_EmployeeID FOREIGN KEY(EmployeeID) REFERENCES Employees(ID),
CONSTRAINT FK_EmployeesBonus_BonusTypeID FOREIGN KEY(BonusTypeID) REFERENCES BonusTypes(ID)
)
GO

Вот такой полигон мы должны были получить в итоге:

Кстати, потом этот полигон (когда он будет наполнен данными) вы и можете использовать для того чтобы опробовать на нем разнообразные запросы – здесь можно опробовать и разнообразные JOIN-соединения, и UNION-объединения, и группировки с агрегированием данных.

INSERT – вставка новых данных

Данный оператор имеет 2 основные формы:

1. INSERT INTO таблица(перечень_полей) VALUES(перечень_значений) – вставка в таблицу новой строки значения полей которой формируются из перечисленных значений
2. INSERT INTO таблица(перечень_полей) SELECT перечень_значений FROM … – вставка в таблицу новых строк, значения которых формируются из значений строк возвращенных запросом.

В диалекте MS SQL слово INTO можно отпускать, что мне очень нравится и я этим всегда пользуюсь.

К тому же стоит отметить, что первая форма в диалекте MS SQL с версии 2008, позволяет вставить в таблицу сразу несколько строк:

INSERT таблица(перечень_полей) VALUES
(перечень_значений1),
(перечень_значений2),
…
(перечень_значенийN)

INSERT – форма 1. Переходим сразу к практике

Наполним таблицу EmployeesSalaryHistory предоставленными нам данными:

INSERT EmployeesSalaryHistory(EmployeeID,DateFrom,DateTo,Salary)
VALUES
  -- Иванов И.И.
  (1000,'20131101','20140531',4000),
  (1000,'20140601','20141230',4500),
  (1000,'20150101',NULL,5000),
  -- Петров П.П.
  (1001,'20131101','20140630',1300),
  (1001,'20140701','20140930',1400),
  (1001,'20141001',NULL,1500),
  -- Сидоров С.С.
  (1002,'20140101',NULL,2500),
  -- Андреев А.А.
  (1003,'20140601',NULL,2000),
  -- Николаев Н.Н.
  (1004,'20140701','20150131',1400),
  (1004,'20150201','20150131',1500),
  -- Александров А.А.
  (1005,'20150101',NULL,2000)

Таким образом мы вставили в таблицу EmployeesSalaryHistory 11 новых записей.

SELECT *
FROM EmployeesSalaryHistory

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

INSERT EmployeesSalaryHistory
VALUES
  -- Иванов И.И.
  (1000,'20131101','20140531',4000),
  (1000,'20140601','20141230',4500),
  (1000,'20150101',NULL,5000),
  …

Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.

Несколько заметок про INSERT:

• Порядок перечисления полей не имеет значения, вы можете написать и (EmployeeID,DateFrom,DateTo,Salary) и (DateFrom,DateTo, EmployeeID,Salary). Здесь важно только то, чтобы он совпадал с порядком значений, которые вы перечисляете в скобках после ключевого слова VALUES.
• Так же важно, чтобы при вставке были заданы значения для всех обязательных полей, которые помечены в таблице как NOT NULL.
• Можно не указывать поля у которых была указана опция IDENTITY или же поля у которых было задано значение по умолчанию при помощи DEFAULT, т.к. в качестве их значения подставится либо значение из счетчика, либо значение, указанное по умолчанию. Такие вставки мы уже делали в первой части.
• В случаях, когда значение поля со счетчиком нужно задать явно используйте опцию IDENTITY_INSERT.

В предыдущих частях мы периодически использовали опцию IDENTITY_INSERT. Давайте и здесь воспользуемся данной опцией для создания строк в таблице BonusTypes, у которой поле ID указано с опцией IDENTITY:

-- даем разрешение на добавление/изменение IDENTITY значения
SET IDENTITY_INSERT BonusTypes ON

INSERT BonusTypes(ID,Name)VALUES
(1,N'Ежемесячный'),
(2,N'Годовой'),
(3,N'Индивидуальный')

-- запрещаем добавление/изменение IDENTITY значения
SET IDENTITY_INSERT BonusTypes OFF

Давайте вставим информацию по начислению сотрудникам ЗП, любезно предоставленную нам бухгалтером:

-- Иванов И.И.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1000,'20131130',4000,NULL),
(1000,'20131231',4000,NULL),
(1000,'20140115',2000,N'Аванс'),
(1000,'20140131',2000,NULL),
(1000,'20140228',4000,NULL),
(1000,'20140331',4000,NULL),
(1000,'20140430',4000,NULL),
(1000,'20140531',4000,NULL),
(1000,'20140630',6500,N'ЗП + Аванс 2500 за 2014.07'),
(1000,'20140731',2000,NULL),
(1000,'20140831',4500,NULL),
(1000,'20140930',4500,NULL),
(1000,'20141031',4500,NULL),
(1000,'20141130',4500,NULL),
(1000,'20141230',4500,NULL),
(1000,'20150131',5000,NULL),
(1000,'20150228',5000,NULL),
(1000,'20150331',5000,NULL)

-- Петров П.П.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1001,'20131130',2600,N'ЗП + ЗП за 2013.12'),
(1001,'20140228',2600,N'За 2 месяца 2014.01, 2014.02'),
(1001,'20140331',1300,NULL),
(1001,'20140430',1300,NULL),
(1001,'20140510',300,N'Аванс'),
(1001,'20140520',500,N'Аванс'),
(1001,'20140531',500,NULL),
(1001,'20140630',1300,NULL),
(1001,'20140731',1400,NULL),
(1001,'20140831',1400,NULL),
(1001,'20140930',1400,NULL),
(1001,'20141031',1500,NULL),
(1001,'20141130',1500,NULL),
(1001,'20141230',3000,N'ЗП + ЗП за 2015.01'),
(1001,'20150228',1500,NULL),
(1001,'20150331',1500,NULL)

-- Сидоров С.С.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1002,'20140131',2500,NULL),
(1002,'20140228',2500,NULL),
(1002,'20140331',2500,NULL),
(1002,'20140430',2500,NULL),
(1002,'20140531',2500,NULL),
(1002,'20140630',2500,NULL),
(1002,'20140731',2500,NULL),
(1002,'20140831',2500,NULL),
(1002,'20140930',2500,NULL),
(1002,'20141031',2500,NULL),
(1002,'20141130',2500,NULL),
(1002,'20141230',2500,NULL),
(1002,'20150131',2500,NULL),
(1002,'20150228',2500,NULL),
(1002,'20150331',2500,NULL)

-- Андреев А.А.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1003,'20140630',2000,NULL),
(1003,'20140731',2000,NULL),
(1003,'20140831',2000,NULL),
(1003,'20140930',2000,NULL),
(1003,'20141031',2000,NULL),
(1003,'20141130',2000,NULL),
(1003,'20141230',2000,NULL),
(1003,'20150131',2000,NULL),
(1003,'20150228',2000,NULL),
(1003,'20150331',2000,NULL)

-- Николаев Н.Н.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1004,'20140731',1400,NULL),
(1004,'20140831',1400,NULL),
(1004,'20140930',1400,NULL),
(1004,'20141031',1400,NULL),
(1004,'20141130',1400,NULL),
(1004,'20141212',400,N'Аванс'),
(1004,'20141230',1400,NULL),
(1004,'20150131',1400,NULL),
(1004,'20150228',1500,NULL),
(1004,'20150331',1500,NULL)

-- Александров А.А.
INSERT EmployeesSalary(EmployeeID,SalaryDate,SalaryAmount,Note)VALUES
(1005,'20150131',2000,NULL),
(1005,'20150228',2000,NULL),
(1005,'20150331',2000,NULL)

Думаю, приводить содержимое таблицы уже нет смысла.

INSERT – форма 2

Данная форма позволяет вставить в таблицу данные полученные запросом.

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

INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent)
-- расчет ежемесячных бонусов
SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent,1 BonusTypeID,emp.BonusPercent
FROM EmployeesSalaryHistory hist
JOIN
  (
    VALUES -- весь период работы компании - последние дни месяцев
      ('20131130'),
      ('20131231'),
      ('20140131'),
      ('20140228'),
      ('20140331'),
      ('20140430'),
      ('20140531'),
      ('20140630'),
      ('20140731'),
      ('20140831'),
      ('20140930'),
      ('20141031'),
      ('20141130'),
      ('20141230'),
      ('20150131'),
      ('20150228'),
      ('20150331')
  ) bdate(BonusDate)
ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
JOIN Employees emp ON hist.EmployeeID=emp.ID
WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0
  AND NOT EXISTS( -- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период
              SELECT *
              FROM
                (
                  VALUES
                    (1001,'20140115'),
                    (1001,'20140430'),
                    (1001,'20141031'),
                    (1001,'20141130'),
                    (1001,'20150228')
                ) exclude(EmployeeID,BonusDate)
              WHERE exclude.EmployeeID=emp.ID
                AND exclude.BonusDate=bdate.BonusDate
            )

UNION ALL

-- годовой бонус за 2014 год - всем кто проработал больше полугода
SELECT
  hist.EmployeeID,
  '20141231' BonusDate,
  hist.Salary/100*
  CASE DepartmentID
    WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
    WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
    ELSE 5 -- всем остальным по 5%
  END BonusAmount,
  2 BonusTypeID,
  CASE DepartmentID
    WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
    WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
    ELSE 5 -- всем остальным по 5%
  END BonusPercent
FROM EmployeesSalaryHistory hist
JOIN Employees emp ON hist.EmployeeID=emp.ID
WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
  AND emp.HireDate<='20140601'

UNION ALL

-- индивидуальные бонусы
SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent
FROM
  (
    VALUES
      (1001,'20140930',300),
      (1002,'20140331',500),
      (1002,'20140630',500),
      (1002,'20140930',500),
      (1002,'20141230',500),
      (1002,'20150331',500),
      (1004,'20140831',200)
  ) indiv(EmployeeID,BonusDate,BonusAmount)

В таблицу EmployeesBonus должно было вставиться 50 записей.

Результат каждого запроса объединенных конструкциями UNION ALL вы можете проанализировать самостоятельно. Если вы хорошо изучили базовые конструкции, то вам должно быть все понятно, кроме возможно конструкции с VALUES (конструктор табличных значений), которая появилась с MS SQL 2008.

Пара слов про конструкцию VALUES

SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent
FROM
  (
    VALUES
      (1001,'20140930',300),
      (1002,'20140331',500),
      (1002,'20140630',500),
      (1002,'20140930',500),
      (1002,'20141230',500),
      (1002,'20150331',500),
      (1004,'20140831',200)
  ) indiv(EmployeeID,BonusDate,BonusAmount)

В случае необходимости, данную конструкцию можно заменить, аналогичным запросом, написанным через UNION ALL:

SELECT 1001 EmployeeID,'20140930' BonusDate,300 BonusAmount,3 BonusTypeID,NULL BonusPercent
UNION ALL
SELECT 1002,'20140331',500,3,NULL
UNION ALL
SELECT 1002,'20140630',500,3,NULL
UNION ALL
SELECT 1002,'20140930',500,3,NULL
UNION ALL
SELECT 1002,'20141230',500,3,NULL
UNION ALL
SELECT 1002,'20150331',500,3,NULL
UNION ALL
SELECT 1004,'20140831',200,3,NULL

Думаю, комментарии излишни и вам не составит большого труда разобраться с этим самостоятельно.

Так что, идем дальше.

INSERT + CTE-выражения

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

Для начала полностью очистим таблицу EmployeesBonus при помощи операции TRUNCATE TABLE:

TRUNCATE TABLE EmployeesBonus

Теперь перепишем запрос вынеся запросы в блок WITH:

WITH cteBonusType1 AS(
  -- расчет ежемесячных бонусов
  SELECT hist.EmployeeID,bdate.BonusDate,hist.Salary/100*emp.BonusPercent BonusAmount,1 BonusTypeID,emp.BonusPercent
  FROM EmployeesSalaryHistory hist
  JOIN
    (
      VALUES -- весь период работы компании - последние дни месяцев
        ('20131130'),
        ('20131231'),
        ('20140131'),
        ('20140228'),
        ('20140331'),
        ('20140430'),
        ('20140531'),
        ('20140630'),
        ('20140731'),
        ('20140831'),
        ('20140930'),
        ('20141031'),
        ('20141130'),
        ('20141230'),
        ('20150131'),
        ('20150228'),
        ('20150331')
    ) bdate(BonusDate)
  ON bdate.BonusDate BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
  JOIN Employees emp ON hist.EmployeeID=emp.ID
  WHERE emp.BonusPercent IS NOT NULL AND emp.BonusPercent>0
    AND NOT EXISTS( -- исключаем сотрудников, которым по какой-то причине не дали бонус в указанный период
                SELECT *
                FROM
                  (
                    VALUES
                      (1001,'20140115'),
                      (1001,'20140430'),
                      (1001,'20141031'),
                      (1001,'20141130'),
                      (1001,'20150228')
                  ) exclude(EmployeeID,BonusDate)
                WHERE exclude.EmployeeID=emp.ID
                  AND exclude.BonusDate=bdate.BonusDate
              )
),
cteBonusType2 AS(
  -- годовой бонус за 2014 год - всем кто проработал больше полугода
  SELECT
    hist.EmployeeID,
    '20141231' BonusDate,
    hist.Salary/100*
    CASE DepartmentID
      WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
      WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
      ELSE 5 -- всем остальным по 5%
    END BonusAmount,
    2 BonusTypeID,
    CASE DepartmentID
      WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
      WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
      ELSE 5 -- всем остальным по 5%
    END BonusPercent
  FROM EmployeesSalaryHistory hist
  JOIN Employees emp ON hist.EmployeeID=emp.ID
  WHERE CAST('20141231' AS date) BETWEEN hist.DateFrom AND ISNULL(hist.DateTo,'20991231')
    AND emp.HireDate<='20140601'
),
cteBonusType3 AS(
  -- индивидуальные бонусы
  SELECT EmployeeID,BonusDate,BonusAmount,3 BonusTypeID,NULL BonusPercent
  FROM
    (
      VALUES
        (1001,'20140930',300),
        (1002,'20140331',500),
        (1002,'20140630',500),
        (1002,'20140930',500),
        (1002,'20141230',500),
        (1002,'20150331',500),
        (1004,'20140831',200)
    ) indiv(EmployeeID,BonusDate,BonusAmount)
)

INSERT EmployeesBonus(EmployeeID,BonusDate,BonusAmount,BonusTypeID,BonusPercent)
SELECT *
FROM cteBonusType1
UNION ALL
SELECT *
FROM cteBonusType2
UNION ALL
SELECT *
FROM cteBonusType3

Как видим вынос больших подзапросов в блок WITH упростил основной запрос – сделал его более понятным.

UPDATE – обновление данных

Данный оператор в MS SQL имеет 2 формы:

1. UPDATE таблица SET … WHERE условие_выборки – обновлении строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут обновлены все строки. Это можно сказать классическая форма оператора UPDATE.
2. UPDATE псевдоним SET … FROM … – обновление данных таблицы участвующей в предложении FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.

Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 6 отдельных операций UPDATE:

-- приведем даты приема в порядок
UPDATE Employees SET HireDate='20131101' WHERE ID=1000

UPDATE Employees SET HireDate='20131101' WHERE ID=1001

UPDATE Employees SET HireDate='20140101' WHERE ID=1002

UPDATE Employees SET HireDate='20140601' WHERE ID=1003

UPDATE Employees SET HireDate='20140701' WHERE ID=1004

-- а здесь еще почистим поле FirstName
UPDATE Employees SET HireDate='20150101',FirstName=NULL WHERE ID=1005

Вторую форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:

UPDATE e
SET
  PositionID=(SELECT ID FROM Positions WHERE Name=e.Position),
  DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department)
FROM Employees e

Сейчас конечно данный и следующий запрос не сработают, т.к. поля Position и Department мы удалили из таблицы Employees. Вот так можно было бы представить этот запрос при помощи операций соединений:

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

Надеюсь суть обновления здесь понятна, тут обновляться будут строки таблицы Employees.

Сначала вы можете сделать выборку, чтобы посмотреть какие данные будут обновлены и на какие значения:

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

А потом переписать это в UPDATE:

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

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

Для этого опять вспомним 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);

Данная конструкция должна оканчиваться «;».

После выполнения запроса сравните 2 таблицы, их данные должны быть одинаковыми.

Конструкция MERGE чем-то напоминает условный оператор CASE, она так же содержит блоки WHEN, при выполнении условий которых происходит то или иное действие, в данном случае удаление (DELETE), обновление (UPDATE) или добавление (INSERT). Модификация данных производится в таблице приемнике.

В качестве источника может выступать запрос. Например, синхронизируем только данные по отделу 3 и для примера исключаем блок «NOT MATCHED BY SOURCE», чтобы данные не удались в случае не совпадения:

MERGE EmployeesBonusTarget trg -- таблица приемник
USING
        (
          SELECT bonus.*
          FROM EmployeesBonus bonus
          JOIN Employees emp ON bonus.EmployeeID=emp.ID
          WHERE emp.DepartmentID=3
        ) src -- источник
ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния

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

Я показал работу конструкции MERGE в самом общем ее виде. При помощи нее можно реализовывать более разнообразные схемы для слияния данных, например, можно включать в блоки WHEN дополнительные условия (WHEN MATCHED AND … THEN). Это очень мощная конструкция, позволяющая в подходящих случаях сократить объем кода и совместить в рамках одного оператора функционал всех трех операторов – INSERT, UPDATE и DELETE.

И естественно с конструкцией MERGE так же можно применять CTE-выражения:

WITH cteBonus AS(
  SELECT bonus.*
  FROM EmployeesBonus bonus
  JOIN Employees emp ON bonus.EmployeeID=emp.ID
  WHERE emp.DepartmentID=3
)
MERGE EmployeesBonusTarget trg -- таблица приемник
USING cteBonus src -- источник
ON trg.EmployeeID=src.EmployeeID AND trg.BonusDate=src.BonusDate AND trg.BonusTypeID=src.BonusTypeID -- условие слияния

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

В общем, я постарался вам задать направление, более подробнее, в случае необходимости, изучайте уже самостоятельно.

TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы

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

Некоторые вырезки из MSDN. TRUNCATE TABLE – удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Если таблица содержит столбец идентификаторов (столбец с опцией IDENTITY), счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.

Инструкцию TRUNCATE TABLE нельзя использовать если на таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.

Пример:

TRUNCATE TABLE EmployeesBonusTarget

Заключение по операциям модификации данных

Здесь я наверно повторю, все что писал ранее.

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

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

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

Помните, что модификация данных это очень серьезно.

Заключение

Вот и все, уважаемые читатели, на этом я оканчиваю свой учебник по SQL (DDL, DML).

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

Учитесь, практикуйтесь, добивайтесь получения правильных результатов.

Спасибо за внимание! На этом пока все.

Автор: Leran2002

Источник ^[5]