SQL - DELETE JOIN

如果有办法将新数据输入任何物体,那么还必须有办法从同一物体中删除不想要的数据。 与这一概念一样,在使用INSERT声明的表格上插入,而删除工作则使用DELETE说明进行。

可在表格的单一记录或多个记录上进行简单删除。 但是,如果要在多个表格的多份记录上进行这一删除行动,将如何进行? 页: 1 情况说明。

DELETE… JOIN in SQL

正如我们先前在本理论中讨论过的那样,通过合并根据共同领域编制的表格的栏目,Joins被利用从两个或两个以上表格中检索记录。 这一合并数据可以删除,所有改动都反映在原表格中。

例如,考虑建立一个教育机构的数据库。 它包括各种表格:部门、学生详情、图书馆通行证、实验室通行证等。 当一组学生毕业时,他们从组织桌上获得的所有细节都需要删除,因为他们是不必要的。 然而,将细节与多个表格分开删除可能很麻烦。

为了便利这一进程,我们将首先从所有表格中检索所有毕业学生的综合数据,使用Joins;然后,从所有表格中删除这一合并数据,使用DELETE说明。 整个过程可以在一个单一地点进行。

Syntax

The following is the basic syntax of DELETE... JOIN ——

DELETE table(s)
FROM table1 JOIN table2
ON table1.common_field = table2.common_field;

当我们在这里说JOIN时,我们可以使用任何类型的Join:经常Join、自然Join、Inner Join、Exer Join、左派Join、Join、Ret Join、Ret Join、Alfonin、Rein、Ad Join等。

Example

为了证明这种删除行动,我们必须首先制定表格,并将价值插入其中。 我们可以使用下文所示的NCREATE TABLE查询创建这些表格。

我们试图建立一个名为客户的表格,其中载有客户的个人详情,包括姓名、年龄、地址和薪金等。 采用以下程序:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在,在表格中添加以下价值观:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

该表将编成:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | Kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

让我们制定另一份表格命令,载列所发布命令的细节和日期。

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
);

采用INSERT声明,在本表中插入以下数值:

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102,  2009-10-08 00:00:00 , 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100,  2009-10-08 00:00:00 , 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101,  2009-11-20 00:00:00 , 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103,  2008-05-20 00:00:00 , 4, 2060.00);

表格如下:

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+

删除这些表格的DELETE...... JOIN query。

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID;

(3 rows affected)

SELECT * FROM Customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SELECT * FROM Orders;

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

(1 row affected)

SELECT * FROM Customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SELECT * FROM Orders;

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
| 100 | 2009-10-08 00:00:00 |           3 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 103 | 2008-05-20 00:00:00 |           4 | 2060.00 |
+-----+---------------------+-------------+---------+