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 используется для удаления данных из базы данных
Use the DELETE statement to remove rows from:
An unpartitioned or partitioned table
The unpartitioned or partitioned base table of a view
The unpartitioned or partitioned container table of a writable materialized view
The unpartitioned or partitioned master table of an updatable materialized view
For you to delete rows from a table, the table must be in your own schema or you must have the DELETE object privilege on the table.
For you to delete rows from an updatable materialized view, the materialized view must be in your own schema or you must have the DELETE object privilege on the materialized view.
For you to delete rows from the base table of a view, the owner of the schema containing the view must have the DELETE object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the DELETE object privilege on the view.
The DELETE ANY TABLE system privilege also allows you to delete rows from any table or table partition or from the base table of any view.
To delete rows from an object on a remote database, you must also have the READ or SELECT object privilege on the object.
If the SQL92_SECURITY initialization parameter is set to TRUE and the DELETE operation references table columns, such as the columns in a where_clause , then you must also have the SELECT object privilege on the object from which you want to delete rows.
You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index.
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
«Hints» for the syntax and description of hints
Use the FROM clause to specify the database objects from which you are deleting rows.
The ONLY syntax is relevant only for views. Use the ONLY clause if the view in the FROM clause belongs to a view hierarchy and you do not want to delete rows from any of its subviews.
Use this clause to specify the objects from which data is being deleted.
Specify the schema containing the table or view. If you omit schema , then Oracle Database assumes the table or view is in your own schema.
table | view | materialized view | subquery
Specify the name of a table, view, materialized view, or the column or columns resulting from a subquery, from which the rows are to be deleted.
When you delete rows from an updatable view, Oracle Database deletes rows from the base table.
You cannot delete rows from a read-only materialized view. If you delete rows from a writable materialized view, then the database removes the rows from the underlying container table. However, the deletions are overwritten at the next refresh operation. If you delete rows from an updatable materialized view that is part of a materialized view group, then the database also removes the corresponding rows from the master table.
If table or the base table of view or the master table of materialized_view contains one or more domain index columns, then this statement executes the appropriate indextype delete routine.
Issuing a DELETE statement against a table fires any DELETE triggers defined on the table.
All table or index space released by the deleted rows is retained by the table and index.
Specify the name or partition key value of the partition or subpartition targeted for deletes within the object.
You need not specify the partition name when deleting values from a partitioned object. However, in some cases, specifying the partition name is more efficient than a complicated where_clause .
Specify the complete or partial name of a database link to a remote database where the object is located. You can delete rows from a remote object only if you are using Oracle Database distributed functionality.
Starting with Oracle Database 12 c Release 2 (12.2), the DELETE statement accepts remote LOB locators as bind variables. Refer to the “Distributed LOBs” chapter in Oracle Database SecureFiles and Large Objects Developer’s Guide for more information.
If you omit dblink , then the database assumes that the object is located on the local database.
The subquery_restriction_clause lets you restrict the subquery in one of the following ways:
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
Specify the name of the CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form SYS_C n , where n is an integer that makes the constraint name unique within the database.
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting .
The optional plus (+) is relevant if you are joining the TABLE collection expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.
In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expression was expressed as THE subquery . That usage is now deprecated.
You can use a table_collection_expression in a correlated subquery to delete rows with values that also exist in another table.
Specify a subquery that selects a nested table column from the object from which you are deleting.
Restrictions on the dml_table_expression_clause Clause
This clause is subject to the following restrictions:
You cannot execute this statement if table or the base or master table of view or materialized_view contains any domain indexes marked IN_PROGRESS or FAILED.
You cannot insert into a partition if any affected index partitions are marked UNUSABLE .
You cannot specify the ORDER BY clause in the subquery of the DML_table_expression_clause .
You cannot delete from a view except through INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
- A set operator
- A DISTINCT operator
- An aggregate or analytic function
- A GROUP BY , ORDER BY , MODEL , CONNECT BY , or START WITH clause
- A collection expression in a SELECT list
- A subquery in a SELECT list
- A subquery designated WITH READ ONLY
- Joins, with some exceptions, as documented in Oracle Database Administrator’s Guide
If you specify an index, index partition, or index subpartition that has been marked UNUSABLE , then the DELETE statement will fail unless the SKIP_UNUSABLE_INDEXES initialization parameter has been set to true .
Provide a correlation name for the table, view, materialized view, subquery, or collection value to be referenced elsewhere in the statement. This alias is required if the DML_table_expression_clause references any object type attributes or object type methods. Table aliases are generally used in DELETE statements with correlated queries.
Use the where_clause to delete only rows that satisfy the condition. The condition can reference the object from which you are deleting and can contain a subquery. You can delete rows from a remote object only if you are using Oracle Database distributed functionality. Refer to Conditions for the syntax of condition .
If this clause contains a subquery that refers to remote objects, then the DELETE operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_table_expression_clause refers to any remote objects, then the DELETE operation will run serially without notification. Refer to the parallel_clause in the CREATE TABLE documentation for additional information.
If you omit dblink , then the database assumes that the table or view is located on the local database.
If you omit the where_clause , then the database deletes all rows of the object.
This clause lets you return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement.
The returning clause retrieves the rows affected by a DML statement. You can specify this clause for tables and materialized views and for views with a single base table.
When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.
Each item in the expr list must be a valid expression syntax.
The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.
For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.
Restrictions on the RETURNING Clause
The following restrictions apply to the RETURNING clause:
The expr is restricted as follows:
For UPDATE and DELETE statements each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause . For INSERT statements, each expr must be a simple expression. Aggregate functions are not supported in an INSERT statement RETURNING clause.
Single-set aggregate function expressions cannot include the DISTINCT keyword.
If the expr list contains a primary key column or other NOT NULL column, then the update statement fails if the table has a BEFORE UPDATE trigger defined on it.
You cannot specify the returning_clause for a multitable insert.
You cannot use this clause with parallel DML or with remote objects.
You cannot retrieve LONG types with this clause.
You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.
Oracle Database PL/SQL Language Reference for information on using the BULK COLLECT clause to return multiple values to collection variables
The error_logging_clause has the same behavior in DELETE statement as it does in an INSERT statement. Refer to the INSERT statement error_logging_clause for more information.
Deleting Rows: Examples
The following statement deletes all rows from the sample table oe.product_descriptions where the value of the language_id column is AR :
The following statement deletes from the sample table hr.employees purchasing clerks whose commission rate is less than 10%:
The following statement has the same effect as the preceding example, but uses a subquery:
Deleting Rows from a Remote Database: Example
The following statement deletes specified rows from the locations table owned by the user hr on a database accessible by the database link remote :
Deleting Nested Table Rows: Example
For an example that deletes nested table rows, refer to «Table Collections: Examples» .
Deleting Rows from a Partition: Example
The following example removes rows from partition sales_q1_1998 of the sh.sales table:
Using the RETURNING Clause: Example
The following example returns column salary from the deleted rows and stores the result in bind variable :bnd1 . The bind variable must already have been declared.
Deleting Data from a Table: Example
The following statements create a table named product_price_history and insert data into it:
The following statement deletes the rows from the table product_price_history where product_id is 3:
The following procedure deletes the rows from the product_price_history where product_id is 2 and effective_to_date is NULL:
The following statement deletes the rows from the table product_price_history where currency_code is ‘EUR’:
The following statement uses a subquery to delete rows from product_price_history:
The following statement uses partitions to delete rows from product_price_history:
The following statement displays the table information:
The following statement deletes all rows from product_price_history:
View and run a related example on Oracle Live SQL at Deleting Data from a Table
Оператор 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.
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!