Transact-SQL - изменение и удаление данных

138 Исходники баз данных

Инструкция UPDATE

Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:

UPDATE tab_name
    { SET column_1 = {expression | DEFAULT | NULL} [,...n]
    [FROM tab_name1 [,...n]]
    [WHERE condition]


Соглашения по синтаксису

Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.

В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:

USE SampleDb;

UPDATE Works_on
    SET Job = 'Менеджер'
    WHERE EmpId = 9031 AND ProjectNumber = 'p3';

В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:

USE SampleDb;

UPDATE Project
    SET Budget = Budget * 0.9;

В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.

В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:

USE SampleDb;

UPDATE Works_on
    SET Job = NULL
    WHERE EmpId IN (SELECT Id
                    FROM Employee
                    WHERE LastName = 'Вершинина');

Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:

USE SampleDb;

UPDATE Works_on
    SET Job = NULL
        FROM Works_on, Employee
        WHERE LastName = 'Вершинина'
            AND Works_on.EmpId = Employee.Id;

В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:

USE SampleDb;

UPDATE Project
    SET Budget = CASE
        WHEN Budget > 0 AND Budget <= 100000 THEN Budget * 1.2
        WHEN Budget > 100000 AND Budget < 150000 THEN Budget * 1.1
        ELSE Budget * 1.05
    END;

Инструкция DELETE

Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:

DELETE FROM table_name
    [WHERE predicate];
    
DELETE table_name
    FROM table_name [,...n]
    [WHERE condition];


Соглашения по синтаксису

Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью 'Менеджер':

USE SampleDb;

DELETE FROM Works_on
    WHERE Job = 'Менеджер';

Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:

USE SampleDb;

DELETE FROM Works_on
    WHERE EmpId IN
        (SELECT Id
         FROM Employee
         WHERE LastName = 'Вершинина');

DELETE FROM Employee
    WHERE LastName = 'Вершинина';

Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.

USE SampleDb;

DELETE Works_on
    FROM Works_on w, Employee e
    WHERE w.EmpId = e.Id
        AND LastName = 'Вершинина';

DELETE FROM Employee
    WHERE LastName = 'Вершинина';

Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:

USE SampleDb;

-- Удаление всех строк таблицы
DELETE FROM Works_on;

Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.

Другие инструкции и предложения Transact-SQL для модификации таблиц

Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:

Эти инструкции и предложение рассматриваются в последующих подразделах.

Инструкция TRUNCATE TABLE

Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.

Инструкция TRUNCATE TABLE имеет следующий синтаксис:

TRUNCATE TABLE table_name

Инструкция MERGE

Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.

Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing). Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.

Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу "запись за записью".

Предложение OUTPUT

По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например "3 rows DELETED" (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT, которое выводит модифицированные, вставленные или удаленные строки.

Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.

Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.

В примере ниже показано использование инструкции OUTPUT с инструкцией DELETE:

USE SampleDb;
DELETE FROM Works_on;

-- В эту переменную будут сохраняться удаленные данные
DECLARE @deleteTable TABLE (Id INT, LastName NCHAR(20));

DELETE Employee
    OUTPUT deleted.Id, deleted.LastName INTO @deleteTable
    WHERE Id > 12000;

SELECT * FROM @deleteTable;

При условии, что содержимое таблицы находится в исходном состоянии, выполнение запроса в примере дает следующий результат:

Использование OUTPUT в инструкции DELETE

В этом примере сначала объявляется табличная переменная @deleteTable с двумя столбцами: Id и LastName. В этой таблице будут сохранены удаленные строки. Синтаксис инструкции DELETE расширен предложением OUTPUT: "OUTPUT deleted.Id, deleted.LastName INTO @deleteTable". Посредством этого предложения система сохраняет удаленные строки в таблице deleted, содержимое которой потом копируется в переменную @deleteTable.

В примере ниже показано использование предложения OUTPUT в инструкции UPDATE:

USE SampleDb;

-- Перед запуском этого примера, нужно
-- будет восстановить исходные данные в базе
DECLARE @updateTable TABLE (Id INT, ProjectNumber NCHAR(20), oldJob NCHAR(15), newJob NCHAR(15));

UPDATE Works_on
    SET Job = 'Менеджер'
        OUTPUT deleted.EmpId, deleted.ProjectNumber,
               deleted.Job, inserted.Job INTO @updateTable
    WHERE Job = 'Консультант';

SELECT Id, ProjectNumber, 
    oldJob 'Старая работа', newJob 'Новая работа'
FROM @updateTable;

Результат выполнения этого запроса:

Использование OUTPUT в инструкции UPDATE
Пройди тесты
Лучший чат для C# программистов