Oracle/PLSQL: DELETE Statement

最新推荐文章于 2024-12-02 08:19:14 发布

原创最新推荐文章于 2024-12-02 08:19:14 发布 · 456 阅读

0 ·

CC 4.0 BY-SA版权

本文为作者的原创文章，如需引用或转载，请标明出处。

文章标签：

#oracle

Oracle 专栏收录该内容

175 篇文章

订阅专栏

SQL DELETE 语句用于从表中删除单条或多条记录。基本语法是 `DELETE FROM table` 后跟 WHERE 子句来指定条件。例如，`DELETE FROM suppliers WHERE supplier_name='IBM'` 将删除所有供应商名为 IBM 的记录。更复杂的删除操作可以通过 SQLEXISTS 子句实现，例如根据另一表的值删除记录。在执行删除前，可以使用 SELECT COUNT(*) 来预览将被删除的记录数。

SQL: DELETE Statement

The SQL DELETE statement allows you to delete a single record or multiple records from a table.

The syntax for the SQL DELETE statement is:

DELETE FROM table

WHERE predicates;

SQL DELETE Statement - One condition example

Let's take a look at a simple example, where we just have one condition in our SQL DELETE statement:

DELETE FROM suppliers

WHERE supplier_name = 'IBM';

This SQL DELETE statement would delete all records from the suppliers table where the supplier_name is IBM.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL SELECT statementbefore performing the delete.

SELECT count(*)

FROM suppliers

WHERE supplier_name = 'IBM';

SQL DELETE Statement - Using SQL EXISTS Clause example

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the SQL FROM clause when you are performing a delete, you can use the SQL EXISTS clause.

For example:

DELETE FROM suppliers

WHERE EXISTS

  ( select customers.name

    from customers

    where customers.customer_id = suppliers.supplier_id

    and customers.customer_name = 'IBM' );

This SQL DELETE statement would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.

If you wish to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

SELECT count(*) FROM suppliers

WHERE EXISTS

  ( select customers.name

    from customers

    where customers.customer_id = suppliers.supplier_id

    and customers.customer_name = 'IBM' );

Frequently Asked Questions

Question: How would I write an SQL DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this for your SQL DELETE statement:

DELETE FROM TableA

WHERE NOT EXISTS

  ( select *

    from TableB

     where TableA.field1 = TableB.fieldx

     and TableA.field2 = TableB.fieldz );