Оператор SQL DELETE для удаления данных из таблицы
Оператор SQL DELETE и удаление данных с условием
Оператор SQL DELETE предназначен для удаления данных из таблицы. Он имеет следующий синтаксис:
Если не указывать условие, из таблицы будут удалены все строки. Кроме того, следует помнить, что могут быть удалены лишь строки с первичными ключами, на которые не ссылаются внешние ключи в других таблицах (более подробно об ограничениях удаления — в уроке Реляционная модель данных).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
А скрипт для создания базы данных «Портал объявлений 1», её таблицы и заполения таблицы данных — в файле по этой ссылке .
Пример 1. Итак, есть база портала объявлений. В ней есть таблица Ads, содержащая данные о объявлениях, поданных за неделю (более подробно — в уроке об агрегатных функциях SQL, пример 7). Таблица выглядит так:
| Id | Category | Part | Units | Money |
| 1 | Транспорт | Автомашины | 110 | 17600 |
| 2 | Недвижимость | Квартиры | 89 | 18690 |
| 3 | Недвижимость | Дачи | 57 | 11970 |
| 4 | Транспорт | Мотоциклы | 131 | 20960 |
| 5 | Стройматериалы | Доски | 68 | 7140 |
| 6 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
| 10 | Недвижимость | Дома | 47 | 9870 |
| 11 | Досуг | Музыка | 117 | 7605 |
| 12 | Досуг | Игры | 41 | 2665 |
Требуется удалить из таблицы строку, имеющую идентификатор 4. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
Пример 2. Можно удалить и несколько строк, если в условии применить оператор сравнения «больше» или «меньше» (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
В результате в таблице останутся лишь следующие строки:
| Id | Category | Part | Units | Money |
| 5 | Стройматериалы | Доски | 68 | 7140 |
| 6 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
| 10 | Недвижимость | Дома | 47 | 9870 |
| 11 | Досуг | Музыка | 117 | 7605 |
| 12 | Досуг | Игры | 41 | 2665 |
Пример 3. Аналогично можно удалять строки с заданными значениями любого столбца. Удалим, например, строки об объявлениях, за которые выручено менее 10000 денежных единиц (запрос на MS SQL Server — с предваряющей конструкцией USE adportal1;):
Пример 4. Чтобы удалить все данные из таблицы ADS, достаточно написать следующий запрос:
Если после выполнения этого запроса обратиться к таблице ADS при помощи оператора SELECT, применяемого для получения выборки данных, то будет выведено сообщение о том, что эта таблица не содержит данных.
Оператору DELETE без условий и ограничений аналогичен оператор TRUNCATE TABLE. Он также удаляет из таблицы все строки, но выполняется намного быстрее.
Пример 5. Запрос на удаление всех данных из таблицы ADS при помощи оператора TRUNCATE TABLE будет следующим (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, UPDATE, HAVING и UNION.
Что характеризует оператора delete
Команда DELETE удаляет из указанной таблицы строки, удовлетворяющие условию WHERE . Если предложение WHERE отсутствует, она удаляет из таблицы все строки, в результате будет получена рабочая, но пустая таблица.
Подсказка
TRUNCATE реализует более быстрый механизм удаления всех строк из таблицы.
Удалить строки в таблице, используя информацию из других таблиц в базе данных, можно двумя способами: применяя вложенные запросы или указав дополнительные таблицы в предложении USING . Выбор предпочитаемого варианта зависит от конкретных обстоятельств.
Предложение RETURNING указывает, что команда DELETE должна вычислить и возвратить значения для каждой фактически удалённой строки. Вычислить в нём можно любое выражение со столбцами целевой таблицы и/или столбцами других таблиц, упомянутых в USING . Список RETURNING имеет тот же синтаксис, что и список результатов SELECT .
Чтобы удалять данные из таблицы, необходимо иметь право DELETE для неё, а также право SELECT для всех таблиц, перечисленных в предложении USING , и таблиц, данные которых считываются в условии .
Параметры
Предложение WITH позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе DELETE . Подробнее об этом см. Раздел 7.8 и SELECT . имя_таблицы
Имя (возможно, дополненное схемой) таблицы, из которой будут удалены строки. Если перед именем таблицы добавлено ONLY , соответствующие строки удаляются только из указанной таблицы. Без ONLY строки будут также удалены из всех таблиц, унаследованных от указанной. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. псевдоним
Альтернативное имя целевой таблицы. Когда указывается это имя, оно полностью скрывает фактическое имя таблицы. Например, в запросе DELETE FROM foo AS f дополнительные компоненты оператора DELETE должны обращаться к целевой таблице по имени f , а не foo . элемент_FROM
Табличное выражение, позволяющее добавить в условие WHERE столбцы из других таблиц. В этом выражении используется тот же синтаксис, что и в предложении FROM оператора SELECT ; например, в нём можно определить псевдоним для таблицы. Повторять в нём имя целевой таблицы нужно, только если требуется определить замкнутое соединение (в этом случае для данного имени должен определяться псевдоним). условие
Выражение, возвращающее значение типа boolean . Удалены будут только те строки, для которых это выражение возвращает true . имя_курсора
Имя курсора, который будет использоваться в условии WHERE CURRENT OF . С таким условием будет удалена строка, выбранная из этого курсора последней. Курсор должен образовываться запросом, не применяющим группировку, к целевой таблице команды DELETE . Заметьте, что WHERE CURRENT OF нельзя задать вместе с логическим условием. За дополнительными сведениями об использовании курсоров с WHERE CURRENT OF обратитесь к DECLARE . выражение_результата
Выражение, которое будет вычисляться и возвращаться командой DELETE после удаления каждой строки. В этом выражении можно использовать имена любых столбцов таблицы имя_таблицы или таблиц, перечисленных в списке USING . Чтобы получить все столбцы, достаточно написать * . имя_результата
Имя, назначаемое возвращаемому столбцу.
Выводимая информация
В случае успешного завершения, DELETE возвращает метку команды в виде
Здесь число — количество удалённых строк. Заметьте, что это число может быть меньше числа строк, соответствующих условию , если удаления были подавлены триггером BEFORE DELETE . Если число равно 0, это означает, что запрос не удалил ни одной строки (это не считается ошибкой).
Если команда DELETE содержит предложение RETURNING , её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING ), полученный для строк, удалённых этой командой.
Замечания
Postgres Pro позволяет ссылаться на столбцы других таблиц в условии WHERE , когда эти таблицы перечисляются в предложении USING . Например, удалить все фильмы определённого продюсера можно так:
По сути в этом запросе выполняется соединение таблиц films и producers , и все успешно включённые в соединение строки в films помечаются для удаления. Этот синтаксис не соответствует стандарту. Следуя стандарту, эту задачу можно решить так:
В ряде случаев запрос в стиле соединения легче написать и он может работать быстрее, чем в стиле вложенного запроса.
Примеры
Удаление всех фильмов, кроме мюзиклов:
Очистка таблицы films :
Удаление завершённых задач с получением всех данных удалённых строк:
Удаление из tasks строки, на которой в текущий момент располагается курсор c_tasks :
SQL Оператор DELETE
SQL оператор DELETE используется для удаления одной или нескольких записей из таблицы.
Синтаксис
Синтаксис оператора DELETE в SQL:
Параметры или аргументы
Примечание
Вам не нужно перечислять поля в операторе DELETE, так как вы удаляете всю строку из таблицы.
Пример оператора DELETE с одним условием
Если вы запустите оператор DELETE без условий в предложении WHERE, все записи из таблицы будут удалены. В результате вы чаще всего будете включать предложение WHERE, по крайней мере с одним условием, в свой оператор DELETE.
Давайте начнем с простого примера запроса DELETE, который имеет одно условие в предложении WHERE.
В этом примере у нас есть таблица suppliers со следующими данными:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Yandex | Moscow | Moscow |
| 200 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | Ile de France |
| 800 | Menlo Park | California | |
| 900 | Electronic Arts | San Francisco | California |
Введите следующий оператор DELETE:
Будет удалена 1 запись. Снова выберите данные из таблицы поставщиков:
Вот результаты, которые вы должны получить:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 200 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | Ile de France |
| 800 | Menlo Park | California | |
| 900 | Electronic Arts | San Francisco | California |
В этом примере удаляются все записи из таблицы suppliers , где supplier_name — Yandex.
Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, выполнив следующий запрос SELECT перед выполнением удаления:
Этот запрос вернет количество записей, которые будут удалены при выполнении оператора DELETE.
| COUNT(*) |
|---|
| 1 |
Пример — оператор DELETE с более чем одним условием
Вы можете иметь более одного условия в инструкции DELETE в SQL, используя либо условие AND, либо условие OR. Условие AND позволяет вам удалить запись, если все условия выполнены. Условие OR удаляет запись, если выполняется одно из условий.
Давайте рассмотрим пример использования оператора DELETE с двумя условиями с использованием условия AND.
В этом примере у нас есть таблица products со следующими данными:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Введите следующий оператор DELETE:
Будет удалены 3 записи. Снова выберите данные из таблицы products :
Вот результаты, которые вы получите:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
В этом примере удаляются все записи из таблицы products , у которых category_id равен 50, а product_name НЕ ‘Pear’.
Пример — использование EXISTS с оператором DELETE
Вы также можете выполнять более сложные удаления.
Вы можете удалить записи в одной таблице на основе значений в другой таблице. Поскольку вы не можете перечислить более одной таблицы в предложении FROM при выполнении удаления, вы можете использовать предложение EXISTS.
SQL DELETE Statement
Up to now, we have learned how to insert and update data in our tables. In this post, we will focus on removing records from a database. This operation is carried out with the SQL DELETE statement.
Executing a DELETE statement
Following what we said in the previous post regarding COMMIT and ROLLBACK, and knowing that in this post we are about to delete items, we will start by executing a COMMIT. Thus, we will store the current state of our database. If necessary, we will be able to revert to it later using ROLLBACK.
In the post about INSERT, under employee number 9–9–9–9–0–3 we added some information about Jonathan Creek. Let’s select his record in the “Employees” table.
Fine — we can see his ‘birthdate’, ‘gender’ and ‘hire date’.
Now, let’s see what information is contained about the same employee in the “Titles” table.
Excellent! His job position is senior engineer, and he started working on the 1st of October 1997. The lack of information in the “to_date” column suggests that he is still working at the company. Good!
As we mentioned at the beginning of the course, the syntax to abide by when deleting information is DELETE FROM, table name, and WHERE with a corresponding condition.
In our case, the code would be DELETE FROM “Employees”, WHERE “Employee number” is 9–9–9–9–0–3.
What should happen when we run this query is that only the row with employee number 9–9–9–9–0–3 will be removed.
Let’s see if this is true after executing this DELETE statement, then selecting the record from the “Employees” table, providing the same condition in the WHERE clause.
So … what output will show up?
Ha! An empty record. This means we have properly deleted the information about Jonathan Creek. Awesome!
What do you think … can we still see he was a senior engineer hired in October 1997? We’ll have to check what’s left in the “Titles” table.
Hmm … empty as well. Why? Didn’t we order a DELETE command for only the “Employees” table?
The answer lies in the connection between the two tables.
When we check the DDL information about the “Titles” table.
We see in the foreign key constraint that we also have an ON DELETE CASCADE clause.
Using this clause means all related records in the child table will be deleted as well. Fantastic!
For the sake of exercise, assume we deleted Jonathan’s information by mistake. Is there a way we can go back? Considering that we applied a COMMIT statement at the beginning of the post, then, yes, there is. We should be able to run a ROLLBACK command.
Let’s execute it … ok!
And now let’s verify that the record has been put back in the table.
It’s in the “Employees” table … ok…
And … we have it in “Titles”, too.
So, our last COMMIT did a good job preserving the full data set — the initial large data set along with the three records we added in the INSERT section.
Keep up the pace for the next section, in which we’ll show you something with which you must be very careful.
Unsafe Delete Operation
If we do not set a condition in the WHERE clause of a DELETE statement, we are taking a big risk. This could potentially lead to the removal of all the table’s records. That’s why we must be very careful when using this statement. Always!
Let’s recall what we have in the “Departments Duplicate” table.
The numbers and names of nine departments in the company.
Let’s execute a DELETE statement without a WHERE clause attached to it.
You see? Nine rows were affected.
Now we can check the table once more.
It couldn’t be emptier than that! ��
To undo the changes, we should be able to execute a ROLLBACK statement. Ok?
And … did it work? We’ll have to select all the information from this tiny table to verify whether we have our data back.
Here it is! Waiting to be retrieved!
Therefore, in conclusion, we can say the following: Be careful with the DELETE statement. Don’t forget to attach a condition in the WHERE clause unless you want to lose all your information.
In the next section, we will compare DROP, DELETE, and TRUNCATE.
DROP vs TRUNCATE vs DELETE
In this section, we will briefly discuss the main difference between three reserved words: DROP, TRUNCATE, and DELETE.
Their functionality is similar, and you might wonder why all of them — and not just one — exist.
Look at this hypothetical table with 10 records.
If you DROP it, you will lose the records, the table as a structure, and all related objects, like indexes and constraints. You will lose everything! Furthermore, you won’t be able to roll back to its initial state, or to the last COMMIT statement. Once you drop a table, it’s gone. Only additional data recovery software will help in such a situation, but it is not considered a standard SQL tool.
Hence, use DROP TABLE only when you are sure you aren’t going to use the table in question anymore.
TRUNCATE
TRUNCATE is a statement that will essentially remove all records from the table, just as if you had used DELETE without a WHERE clause. This means TRUNCATE will remove all records in your table, but its structure will remain intact.
Please bear in mind that when truncating, auto-increment values will be reset. So, if your table has 10 records and then you truncate it, when you start re-filling this data object with information, the next records that will be inserted are not going to be 11 and 12. The first record will be number 1, the second record will be number 2, and so on. Nice!
DELETE
Finally, DELETE removes records row by row. Only the rows corresponding to a certain condition, or conditions, specified in the WHERE clause will be deleted.
If the WHERE block is omitted, the output will resemble the one obtained with TRUNCATE. There will be a couple of significant distinctions, though.
First, the SQL optimizer will implement different programmatic approaches when we are using TRUNCATE or DELETE. As a result, TRUNCATE delivers the output much quicker than DELETE because it does not need to remove information row by row.
Second, auto-increment values are not reset with DELETE. So, if you DELETE all 10 records in this table and then start inserting new data, the first new record will be numbered 11, and not 1; the second will be 12, and not 2, and so on.
There are many other technical peculiarities regarding these three options but their detailed explanation is beyond the scope of this post. Nevertheless, we hope this post will help you make a more educated choice among DROP, TRUNCATE, and DELETE.
Then, fasten your seatbelts, as the next post is about the SQL Best Practices!