ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse
Удаляет одну или несколько строк из таблицы или представления в SQL Server.
Syntax for SQL Server and Azure SQL Database
[ WITH
WITH <обобщенноетабличное
выражение>
Задает временный именованный результирующий набор, также называемый обобщенным табличным выражением, который определяется в области действия инструкции DELETE. Результирующий набор получается из инструкции SELECT.
Обобщенные табличные выражения также можно использовать в инструкциях SELECT, INSERT, UPDATE и CREATE VIEW. Дополнительные сведения см. в разделе .
TOP (expression
)
[ PERCENT ]
Задает количество или процент удаляемых случайных строк. expression
может быть либо числом, либо процентом от числа строк. Строки, на которые ссылается выражение TOP, используемое с инструкциями INSERT, UPDATE и DELETE, не упорядочиваются. Дополнительные сведения см. в разделе
.
FROM
Необязательное ключевое слово, которое можно использовать между ключевым словом DELETE и целевым table_or_view_name
, или rowset_function_limited
.
table_alias
Псевдоним, заданный в предложении table_source
предложение, представляющем таблицу или представление, из которой должны быть удалены строки.
имя_сервера
Имя сервера (с использованием имени связанного сервера или функционировать в качестве имени сервера) на котором расположена таблица или представление. Если имя_сервера указан, имябазы данных и schema_name являются обязательными.
имябазы
данных
Имя базы данных.
schema_name
Имя схемы, которой принадлежит таблица или представление.
table_or view_name
Имя таблицы или представления, откуда удаляются строки.
Табличную переменную в пределах ее области действия также можно использовать в качестве источника таблицы в инструкции DELETE.
Представление ссылается table_or_view_name должно быть обновляемым и ссылаться ровно одну базовую таблицу в предложении FROM определения представления. Дополнительные сведения об обновляемых представлениях см. в разделе .
rowset_function_limited
Возвращает удаленные строки или выражения, основанные на них, как часть операции DELETE. Предложение OUTPUT не поддерживается ни в каких инструкциях DML, направленных на представления и удаленные таблицы. Дополнительные сведения см. в разделе
.
ИЗ table_source
Задает дополнительное предложение FROM. Это Transact-SQL расширение DELETE позволяет задавать данные из
Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк.
Дополнительные сведения см. в разделе .
WHERE
Указывает условия, используемые для ограничения числа удаляемых строк. Если предложение WHERE не указывается, инструкция DELETE удаляет все строки из таблицы.
Предусмотрено два вида операций удаления в соответствии с тем, что указывается в предложении WHERE.
Операции удаления с поиском указывают условие поиска для уточнения строк, которые будут удалены. Например, ГДЕ column_name = значение .
Операции удаления по позиции используют предложение CURRENT OF для указания курсора. Удаление осуществляется в текущей позиции курсора. Это может быть более точной, чем инструкция DELETE по найденному, использующая предложение search_condition предложение для указания удаляемых строк. Инструкция DELETE по найденному удаляет несколько строк, если условие поиска не определяет уникально одну строку.
Указывает ограничивающие условия для удаляемых строк. Количество предикатов, которое может содержать условие поиска, не ограничено. Дополнительные сведения см. в разделе
.
CURRENT OF
Указывает выполнение инструкции DELETE в текущей позиции указанного курсора.
GLOBAL
Указывает, что cursor_name
ссылается на глобальный курсор.
cursor_name
Имя открытого курсора, из которого производится выборка. Если как глобальный, так и локальный курсор с именем cursor_name
существует, этот аргумент ссылается на глобальный курсор, если GLOBAL указанного; в противном случае, он ссылается на локальный курсор. Курсор должен позволять производить обновления.
cursor_variable_name
Имя переменной курсора. Переменная курсора должна содержать ссылку на курсор, обновления которого разрешены.
OPTION (
Ключевые слова, которые указывают, что подсказки оптимизатора применяются при настройке способа Компонент Database Engine обрабатывает инструкцию. Дополнительные сведения см. в разделе
.
Для удаления всех строк в таблице воспользуйтесь инструкцией TRUNCATE TABLE. Инструкция TRUNCATE TABLE выполняется быстрее, чем инструкция DELETE, и использует меньше системных ресурсов и ресурсов журнала транзакций. Инструкция TRUNCATE TABLE имеет ограничения, например, таблица не может участвовать в репликации. Дополнительные сведения см. в разделе
Используйте @@ROWCOUNT функцию для возврата количества удаленных строк клиентскому приложению. Дополнительные сведения см. в разделе .
Для инструкции DELETE можно реализовать обработку ошибок, заключив ее в конструкцию TRY…CATCH.
При выполнении инструкции DELETE может произойти ошибка, если она нарушает триггер или пытается удалить строку, на которую ссылаются данные в другой таблице с помощью ограничения FOREIGN KEY. Если инструкция DELETE удаляет несколько строк и одна из удаленных строк нарушает триггер или ограничение, то эта инструкция отменяется, т.е. возвращается ошибка и строки не удаляются.
В случае арифметической ошибки (переполнение, деление на ноль или выход за пределы допустимых значений), возникающей в ходе вычисления выражения при выполнении инструкции DELETE, компонент Компонент Database Engine будет обрабатывать эти ошибки, как если бы параметр SET ARITHABORT имел значение ON. Оставшаяся часть пакетной операции отменяется и возвращается сообщение об ошибке.
Инструкцию DELETE можно использовать в тексте определяемой пользователем функции, если изменяемым объектом является табличная переменная.
При удалении строки, содержащей столбец FILESTREAM, также удаляются и связанные с ней файлы файловой системы. Базовые файлы удаляются сборщиком мусора FILESTREAM. Дополнительные сведения см. в разделе .
Предложение FROM нельзя указывать в инструкции DELETE, ссылающейся (прямо или косвенно) на представление, в котором указан триггер INSTEAD OF. Дополнительные сведения о триггерах INSTEAD разделе .
При использовании выражения TOP в инструкции DELETE строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этой инструкции. Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. См. подраздел «Примеры» далее в этом разделе.
Предложение TOP не может быть использовано вместе с инструкцией DELETE для секционированных представлений.
По умолчанию инструкция DELETE всегда получает монопольную блокировку (X) на таблицу, которую она изменяет, и держит блокировку до тех пор, пока транзакция не завершится. Если ресурс удерживается монопольной (X) блокировкой, то другие транзакции не могут изменять данные. Операции считывания будут допускаться только при наличии подсказки NOLOCK или уровня изоляции незафиксированной операции чтения. Можно переопределить поведение оптимизатора запросов по умолчанию с помощью табличных подсказок на время выполнения инструкции DELETE указанием другого способа блокировки, но использовать подсказки рекомендуется только опытным разработчикам и администраторам баз данных и только при крайней необходимости. Дополнительные сведения см. в разделе .
Когда строки удаляются из кучи, компонент Компонент Database Engine может использовать строку или страницу блокировки для операции. В результате пустые страницы, в которых выполняются операции удаления, остаются размещенными для кучи. Если их не освободить, занимаемое ими место не может быть использовано под другие объекты базы данных.
Чтобы удалить из кучи строки и освободить страницы, воспользуйтесь одним из следующих методов.
Задайте указания TABLOCK в инструкции DELETE. Использование TABLOCK приведет к тому, что при выполнении операции удаления будет установлена монопольная блокировка таблицы, а не блокировка строки или страницы, что позволит освободить страницы. Дополнительные сведения о подсказке TABLOCK см. в разделе .
Если из таблицы удаляются все строки, пользуйтесь инструкцией TRUNCATE TABLE.
Перед удалением строк создайте в куче кластеризованный индекс. Потом его можно будет удалить. Этот метод потребует больше времени и потребляет больше временных ресурсов.
Инструкция DELETE всегда полностью регистрируется в журнале.
Permissions
Разрешения DELETE необходимы для целевой таблицы. Разрешения SELECT также необходимы, если инструкция содержит предложение WHERE.
Удаление разрешения по умолчанию для членов sysadmin фиксированной серверной роли db_owner и db_datawriter фиксированных ролей базы данных и владельца таблицы. Члены sysadmin , db_owner и db_securityadmin ролей, а также владелец таблицы могут передавать разрешения другим пользователям.
Базовый синтаксис
В примерах в этом разделе описывается базовая функциональность инструкции DELETE с помощью минимального необходимого синтаксиса.
А. Использование инструкции DELETE без предложения WHERE
Следующий пример удаляет все строки из таблицы SalesPersonQuotaHistory в базе данных AdventureWorks2012 , поскольку не указано предложение WHERE, ограничивающее количество удаляемых строк.
DELETE FROM Sales.SalesPersonQuotaHistory; GO
Ограничение удаляемых строк
В примерах в этом разделе описываются способы ограничения количества удаляемых строк.
A. Использование предложения WHERE для удаления набора строк
Следующий пример удаляет все строки таблицы ProductCostHistory в базе данных AdventureWorks2012 , у которых значение в столбце StandardCost больше 1000.00 .
В следующем примере показано использование более сложного предложения WHERE. Предложение WHERE определяет два условия, которые должны быть выполнены для определения удаляемых строк. Значение в столбце StandardCost должно быть в диапазоне от 12.00 до 14.00 , а значение в столбце SellEndDate должно быть равно NULL. В этом примере также выводится значение из **@@ROWCOUNT ** функцию для возврата количества удаленных строк.
Б. Использование курсора для определения удаляемой строки
Следующий пример удаляет одну строку из EmployeePayHistory в таблице AdventureWorks2012 базы данных с помощью курсора my _ cursor . Операция удаления затрагивает только одну строку, выбранную в данный момент курсором.
DECLARE complex_cursor CURSOR FOR SELECT a.BusinessEntityID FROM HumanResources.EmployeePayHistory AS a WHERE RateChangeDate <> (SELECT MAX (RateChangeDate) FROM HumanResources.EmployeePayHistory AS b WHERE a.BusinessEntityID = b.BusinessEntityID) ; OPEN complex_cursor; FETCH FROM complex_cursor; DELETE FROM HumanResources.EmployeePayHistory WHERE CURRENT OF complex_cursor; CLOSE complex_cursor; DEALLOCATE complex_cursor; GO
В. Использование операторов объединения и вложенных запросов к данным в одной таблице для удаления строк в другой таблице
В следующих примерах показано два способа удаления строк в одной таблице на основании данных в другой таблице. В обоих примерах строки из SalesPersonQuotaHistory в таблице AdventureWorks2012 удалить базу данных на основе продаж с начала года, хранящиеся в SalesPerson таблицы. Первый DELETE инструкции показано решение совместимый ISO вложенный запрос, а второй - DELETE инструкции Transact-SQL из расширения для соединения двух таблиц.
A. Ограничение числа удаляемых строк с помощью ключевого слова TOP
Когда TOP (n ) с помощью удаления используется предложение, операция удаления производится над случайно выбранных n строк. Следующий пример удаляет 20 случайных строк из таблицы PurchaseOrderDetail в базе данных AdventureWorks2012 , имеющих дату ранее 1 июля 2006 г.:
Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. Следующий запрос удаляет из таблицы PurchaseOrderDetail 10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки (PurchaseOrderID) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.
Удаление строк из удаленной таблицы
В примерах в этом разделе описаны способы удаления строк из удаленной таблицы с использованием в качестве ссылки на удаленную таблицу или . Удаленная таблица существует на другом сервере или экземпляре SQL Server.
| Применимо к : от SQL Server 2008 до SQL Server 2016. |
A. Удаление данных из удаленной таблицы с помощью связанного сервера
В следующем примере будет удалена строка из удаленной таблицы. Пример начинается с создания ссылки на удаленный источник данных с помощью . Имя связанного сервера, MyLinkServer , затем определяется как часть четырехчастного имени объекта в виде server.catalog.schema.object .
USE master; GO -- Create a link to the remote data source. -- Specify a valid server name for @datasrc as "server_name" or "server_name\instance_name" . EXEC sp_addlinkedserver @server = N"MyLinkServer" , @srvproduct = N" " , @provider = N"SQLNCLI" , @datasrc = N"server_name" , @catalog = N"AdventureWorks2012" ; GO
Б. Удаление данных из удаленной таблицы с помощью функции OPENQUERY
В следующем примере выполняется удаление строк из удаленной таблицы с помощью вызова функции , возвращающей набор строк. В этом примере используется имя связанного сервера, созданного в предыдущем примере.
В. Удаление данных из удаленной таблицы с помощью функции OPENDATASOURCE
В следующем примере выполняется удаление строк из удаленной таблицы с помощью вызова функции , возвращающей набор строк. Укажите допустимое имя сервера для источника данных с использованием формата имя_сервера или server_name\instance_name .
Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:
Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.
В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:
В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:
USE SampleDb; UPDATE Project SET Budget = Budget * 0.9;
В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.
В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:
Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:
В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:
USE SampleDb; UPDATE Project SET Budget = CASE WHEN Budget > 0 AND Budget 100000 AND Budget
Инструкция DELETE
Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:
Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью "Менеджер":
Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:
Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.
Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:
USE SampleDb; -- Удаление всех строк таблицы DELETE FROM Works_on;
Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.
Другие инструкции и предложения Transact-SQL для модификации таблиц
Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:
инструкцию TRUNCATE TABLE;
инструкцию MERGE;
предложение OUTPUT.
Эти инструкции и предложение рассматриваются в последующих подразделах.
Инструкция 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. Поэтому для сохранения результата используется табличная переменная.
Команда TRUNCATE имеет следующий синтаксис:
TRUNCATE TABLE имя_таблицы;
Пример 1
Если нужно полностью очистить таблицу tovar, то следует выполнить команду:
TRUNCATE TABLE tovar;
Команда TRUNCATE позволяет очистить таблицу сразу и полностью и не позволяет удалять отдельные строки, удовлетворяющие каким-либо условиям.
Команда DELETE удаляет из таблицы записи, удовлетворяющие некоторому условию. Она выполняет удаление двух видов:
- Удаление из одной таблицы;
- Каскадное удаление из нескольких таблиц.
Удаление из одной таблицы
DELETE FROM имя_таблицы
;
- Если указана инструкция LOW_PRIORITY, то удаление не произойдет, пока все пользователи не завершат чтение таблицы.
- Инструкция QUICK запрещает объединять индексы при выполнении удаления. Это несколько ускоряет процесс удаления.
- Инструкция ORDER BY позволяет упорядочить записи перед удалением. Сама по себе эта опция не имеет смысла. Ее полезно использовать в сочетании с инструкцией LIMIT.
- Инструкция LIMIT задает количество удаляемых записей.
Каскадное удаление
При каскадном удалении удаляется не только основная запись, но и записи из других таблиц, содержащие внешний ключ основной записи. В MySQL существует два вида таблиц:
- InnoDB, которые поддерживают механизм связи по внешним ключам.
- MyISAM, которые не поддерживают механизма связи по внешним ключам. В этом случае все связи хранятся исключительно в памяти администратора базы данных и программистов.
Для таблиц InnoDB при построении внешнего ключа всегда описывается реакция СУБД на удаление связанных записей. Реакция может быть трех видов:
- RESTRICT – ограничение;
- CASCADE – каскадное удаление;
- SET NULL – установить значение NULL вместо значений удаленного внешнего ключа.
Пример 4
Пусть имеется таблица tovar, содержащая внешний ключ - categ со ссылкой на таблицу category.
Таким образом, каждый товар относится к определенной категории:
Необходимо удалить категорию «Лакокрасочные» из таблицы category и каскадно удалить все связанные с ней записи из таблицы tovar. Если во время создания внешнего ключа categ была указана реакция CASCADE при удалении, то для решения задачи достаточно выполнить команду:
DELETE FROM category WHERE category.categ_name=”лакокрасочные”;
Все товары, относящиеся к категории «лакокрасочные» автоматически удалятся из связанной таблицы tovar.
Однако, если пользователь работает с таблицами MyIsam, то СУБД не знает ни о каких внешних ключах и каскадного удаления не произойдет. В этом случае пригодятся специальные формы команды DELETE, предназначенные для каскадного удаления:
DELETE имя_таблицы [ ,имя_таблицы...]
DELETE
FROM имя_таблицы, [имя_таблицы...] USING таблица_ссылка
Пример 5
Задачу из примера 4 можно решить двумя эквивалентными командами:
DELETE tovar, category FROM tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
DELETE from tovar, category using tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
Замечание 1
На первый взгляд кажется, что команды
TRUNCATE имя_таблицы;
DELETE FROM имя_таблицы;
совершенно эквивалентны. Действительно, они обе полностью очищают таблицу от всех записей. Однако, разница все же есть.
Если используется TRUNCATE, то удаляются сразу все записи и при повторном заполнении таблицы поля с автоинкрементом получат значения начиная с 1. Если используется DELETE, то удаление происходит по одной записи и при повторном заполнении таблицы поля с автоинкрементом получат значение на 1 больше последнего удаленного значения (то есть автоинкрементирование продолжится).
DELETE - это DM-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением WHERE . В случае, если критерий отбора не определён, выполняется удаление всех записей. Синтаксис:
DELETE FROM ;
Более быстро операцию удаление всех строк из таблицы можно в Transact-SQL также выполнить с помощью команды:
TRUNCATE TABLE
Transact-SQL (T-SQL) - процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.
Примеры работы оператора
Пример 1. Требуется удалить из таблицы Laptop все портативные компьютеры с размером экрана менее 15 дюймов.
DELETE FROM Laptop WHERE screen
Все блокноты можно удалить с помощью оператора:
DELETE FROM Laptop;
Transact-SQL расширяет синтаксис оператора DELETE, вводя дополнительное предложение FROM:
При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк. Рассмотрим пример:
Пример 2. Пусть требуется удалить те модели ноутбуков из таблицы Product, для которых нет соответствующих строк в таблице Notebook. Используя стандартный синтаксис, задача решается так:
DELETE FROM Notebook WHERE type = "nb" AND model NOT IN (SELECT model FROM NB);
Предикат type = "Nb" необходим здесь, чтобы не были удалены также модели принтеров, сканеров и персональных компьютеров.
Эту же задачу можно решить с помощью дополнительного предложения FROM следующим образом:
DELETE FROM Notebook FROM Product pr LEFT JOIN NB ON pr.model = NB.model WHERE type = "nb" AND NB.model IS NULL;
Здесь применяется внешнее соединение, в результате чего столбец NB.model для моделей ноутбуков, отсутствующих в таблице NB, будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.
Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис
UPDATE <имя таблицы>
SET {имя столбца = {выражение для вычисления значения столбца
| NULL
| DEFAULT},...}
[ {WHERE <предикат>}];
С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.
Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT ) для данного столбца.
Естественно, типы данных столбцов hd и ram должны быть совместимы. Для приведения типов может использоваться выражение CAST .
Если требуется изменять данные в зависимости от содержимого некоторого столбца, можно воспользоваться выражением CASE . Если, скажем, нужно поставить жесткие диски объемом 20 Гб на ПК-блокноты с памятью менее 128 Мб и 40 гигабайтные - на остальные ПК-блокноты, то можно написать такой запрос:
Необходимо сказать несколько слов об автоинкрементируемых столбцах. Если столбец code в таблице Laptop определен как IDENTITY(1,1) , то следующий оператор
Разумеется, другой строки со значением code =5 в таблице быть не должно.
В Transact-SQL оператор UPDATE расширяет стандарт за счет использования необязательного предложения FROM . В этом предложении специфицируется таблица, обеспечивающая критерий для операции обновления. Дополнительную гибкость здесь дает использование операций соединения таблиц.
Пример . Пусть требуется указать "No PC" (нет ПК) в столбце type для тех моделей ПК из таблицы Product PC . Решение посредством соединения таблиц можно записать так:
Оператор DELETE
Оператор DELETE удаляет строки из временных или постоянных базовых таблиц, представлений или курсоров, причем в двух последних случаях действие оператора распространяется на те базовые таблицы, из которых извлекались данные в эти представления или курсоры. Оператор удаления имеет простой синтаксис:
DELETE FROM <имя таблицы > ;
Если предложение WHERE отсутствует, удаляются все строки из таблицы или представления (представление должно быть обновляемым). Более быстро эту операцию (удаление всех строк из таблицы) в Transact-SQL можно также выполнить с помощью команды
Однако есть ряд отличий в реализации команды TRUNCATE TABLE по сравнению с использованием оператора DELETE , которые следует иметь в виду:
1. Не журнализируется удаление отдельных строк таблицы. В журнал записывается только освобождение страниц, которые были заняты данными таблицы.
2. Не отрабатывают триггеры, в частности, триггер на удаление.
3. Команда неприменима, если на данную таблицу имеется ссылка по внешнему ключу, и даже если внешний ключ имеет опцию каскадного удаления.
4. Значение счетчика (IDENTITY
) сбрасывается в начальное значение.
Пример . Требуется удалить из таблицы Laptop все ПК-блокноты с размером экрана менее 12 дюймов.
| TRUNCATE TABLE Laptop |
Transact-SQL расширяет синтаксис оператора DELETE , вводя дополнительное предложение FROM
FROM <источник табличного типа>
При помощи источника табличного типа можно конкретизировать данные, удаляемые из таблицы в первом предложении FROM .
При помощи этого предложения можно выполнять соединения таблиц, что логически заменяет использование подзапросов в предложении WHERE для идентификации удаляемых строк.
Поясним сказанное на примере. Пусть требуется удалить те модели ПК из таблицы Product , для которых нет соответствующих строк в таблице PC .
Используя стандартный синтаксис, эту задачу можно решить следующим запросом:
Здесь используется внешнее соединение, в результате чего столбец pc.model для моделей ПК, отсутствующих в таблице PC , будет содержать NULL-значение, что и используется для идентификации подлежащих удалению строк.