Оператор DELETE

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

DELETE FROM <имя таблицы >
[WHERE <предикат>];

Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) можно в Transact-SQL также выполнить с помощью команды

TRUNCATE TABLE <имя таблицы>

Однако есть ряд особенностей в реализации команды TRUNCATE TABLE, которые следует иметь в виду:

• не журнализируется удаление отдельных строк таблицы; в журнал записывается только освобождение страниц, которые были заняты данными таблицы;

• не отрабатывают триггеры, в частности, триггер на удаление;

• команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу, и даже если внешний ключ имеет опцию каскадного удаления.

• значение счетчика (IDENTITY) сбрасывается в начальное значение.

Пример 6.3.1

DELETE FROM Laptop
WHERE screen < 12;

Все блокноты можно удалить с помощью оператора

DELETE FROM Laptop;

или

TRUNCATE TABLE Laptop;

Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM:

FROM <источник табличного типа>

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

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

Пример 6.3.2

Используя стандартный синтаксис, эту задачу можно решить следующим запросом:

DELETE FROM Product
WHERE type = 'pc' AND
model NOT IN (SELECT model
FROM PC
);

Заметим, что предикат type = ‘pc’ необходим здесь, чтобы не были удалены также модели принтеров и портативных компьютеров.

Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:

DELETE FROM Product
FROM Product pr LEFT JOIN
PC ON pr.model = PC.model
WHERE type = 'pc' AND
PC.model IS NULL;

Здесь применяется внешнее соединение, в результате чего столбец PC.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.

Рекомендуемые упражнения: 5, 6, 8, 14, 16

Оператор TRUNCATE TABLE

Как отмечалось выше, при выполнении этой команды значение счетчика (IDENTITY) сбрасывается в начальное значение. Давайте проверим это утверждение в MS SQL Server. Для начала создадим таблицу с автоинкрементируемым столбцом, и добавим в нее три строки.

CREATE TABLE Truncate_test (id INT IDENTITY(5,5) PRIMARY KEY, val INT);  
GO  
INSERT INTO Truncate_test(val)   
VALUES (1),(2),(3);  
SELECT * FROM Truncate_test;  
GO

Начальным значением счетчика является 5, приращение счетчика выполняется также с шагом 5.
В результате получим:

Теперь удалим строки с помощью оператора DELETE, после чего снова вставим те же строки в таблицу.

DELETE FROM Truncate_test;
GO
INSERT INTO Truncate_test(val)
VALUES (1), (2), (3);
SELECT * FROM Truncate_test;
GO

Как видно из результата, состояние счетчика не было сброшено, и приращение продолжилось с последнего значения (15), в отличие от использования оператора TRUNCATE TABLE: 

TRUNCATE TABLE Truncate_test;   
GO  
INSERT INTO Truncate_test(val)   
VALUES (1),(2),(3);  
SELECT * FROM Truncate_test;  
GO

В то же время Стандарт предполагает несколько иное поведение. Стандартный синтаксис имеет вид

TRUNCATE TABLE < имя таблицы > [{CONTINUE IDENTITY} | {RESTART IDENTITY}]

т.е. значение счетчика может быть сброшено (опция RESTART IDENTITY) или продолжено (опция CONTINUE IDENTITY). И, кстати, значением по умолчанию является как раз CONTINUE IDENTITY, что эквивалентно поведению при использованию оператора DELETE (без предложения WHERE).

Оператор TRUNCATE TABLE неприменим, если на таблицу имеется ссылка по внешнему ключу. Это стандартное поведение имеет место в SQL Server. Если создать, например, такую ссылающуюся таблицу, которая даже не будет содержать данных

CREATE TABLE Trun_Ref(id INT REFERENCES Truncate_test);

оператор TRUNCATE TABLE приведет к следующей ошибке:

Теперь проверим, насколько близки к стандарту другие реализации.

PostgreSQL

1. Поддерживаются опции CONTINUE IDENTITY и RESTART IDENTITY, при этом опция CONTINUE IDENTITY принимается по умолчанию.
2. Можно удалить одним оператором строки из нескольких таблиц, перечислив их через запятую.
3. Допускаются каскадные операции, т.е. усечение связанных таблиц:

TRUNCATE TABLE Truncate_test RESTART IDENTITY CASCADE;

Причем происходит именно усечение, а не удаление связанных строк. Т.е. если вставить в ссылающуюся таблицу среди прочих строку с NULL-значением во внешнем ключе

INSERT INTO Trun_Ref VALUES(1), (2), (NULL);

то она также будет удалена наряду с остальными.

Oracle

1. В Oracle нет функции для автоинкремента, которую можно указать в определении столбца. Однако поведение автоинкремента можно сымитировать с помощью последовательности (SEQUENCE). Например, подобную ранее рассмотренной таблицу Truncate_test в Oracle можно создать следующим образом:

CREATE SEQUENCE u_seq  
    START WITH     5  
    INCREMENT BY   5;  
   /  
   CREATE TABLE Truncate_test (id INT PRIMARY KEY, val int);  
   /  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,1);  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,2);  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,3);

2. При выполнении оператора TRUNCATE TABLE Truncate_test состояние счетчика (последовательности) не сбрасывается, и нумерация будет продолжена.
3. Каскадные операции не допускаются, т.е. оператор неприменим, если на таблицу есть ссылка по внешнему ключу.

MySQL

1. Не поддерживаются опции CONTINUE IDENTITY и RESTART IDENTITY, при этом состояние счетчика (AUTO_INCREMENT) сбрасывается.
2. Допускаются каскадные операции по аналогичии с оператором DELETE, т.е. если внешний ключ имеет опцию ON DELETE CASCADE, то удаляются только связанные записи. Это значит, что строки с NULL-значением внешнего ключа остаются после выполнения оператора

   TRUNCATE TABLE Truncate_test;