SQL DELETE statement and simultaneous deletion of multi-table data implementation method

SQL DELETE statement and simultaneous deletion of multi-table data implementation method
Delete
[From]
{table_name with (< table_hint_limited > [... n])
| View_name
| rowset_function_limited
}

[From {< Table_source >} [,... N]]

[Where
{< search_condition >
| {[Current OF
{{[Global] cursor_name}
| Cursor_variable_name
}
] }
}
]

From

is an optional keyword that can be used between the DELETE keyword and the target table_name, view_name, or rowset_function_limited.

table_name

is the name of the table from which the row is to be deleted.

A table variable within its scope, or a four-part table name (or view name) that uses the OPENDATASOURCE function as the server name, can also be used as a form source in a DELETE statement.

With (<table_hint_limited> [... n])

Specifies one or more table hints that are allowed by the destination table. The WITH keyword and parentheses are required. Readpast, Nolock and readuncommitted are not allowed. For more information about table hints

Multi-table relational SQL DELETE statement

MySQL Tutorials > CREATE TABLE employee (
   ->     id             int,
   ->     first_ name    varchar,
   ->     last_name      varchar,
   ->     start_date    Date,
   ->     end_date      date,
    ->     salary        Float (8,2),
    ->     city          varchar (ten),
   ->     description   varchar (a)
   ->);
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql> CREATE TABLE Job (
-> ID int,
-> title varchar (20)
->);
Query OK, 0 rows affected (0.05 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (4, ' Celia ', ' rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> values (5, ' Robert ', ' Black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (6, ' Linda ', ' green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> INSERT into employee (Id,first_name, last_name, start_date, end_date, salary, city, description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
mysql> INSERT into Job (ID, title) VALUES (1, ' tester ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (2, ' accountant ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into Job (ID, title) VALUES (3, ' Developer ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (4, ' coder ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (5, ' director ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into Job (ID, title) VALUES (6, ' mediator ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (7, ' proffessor ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (8, ' Programmer ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT into Job (ID, title) VALUES (9, ' Developer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> select * from Job;
+------+------------+
| ID | Title |
+------+------------+
| 1 | Tester |
| 2 | Accountant |
| 3 | Developer |
| 4 | Coder |
| 5 | Director |
| 6 | Mediator |
| 9 2 Proffessor |
| 8 | Programmer |
| 9 | Developer |
+------+------------+
9 Rows in Set (0.00 sec)

Mysql> SELECT * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> Delete Employee
-> from employee, job
-> WHERE (employee.id = job.id)
-> and (job.id = 3);
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql>
Mysql> SELECT * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
7 Rows in Set (0.00 sec)

Method Two

mysql> Delete AB
-> from Authorbook as AB, authors as a
-> where Ab.authid=a.authid and authln= ' a ';
Query OK, 1 row affected (0.05 sec)

Mysql>
mysql> drop table Authorbook;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table books;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table authors;
Query OK, 0 rows affected (0.06 sec)

Delete two tables at the same time

Mysql> Delete employee, job
-> from employee, job
-> WHERE (employee.id = job.id)
-> and (job.id = 3);
Query OK, 2 rows affected (0.01 sec)

Mysql>
Mysql>
Mysql>
Mysql> SELECT * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
7 Rows in Set (0.00 sec)

Mysql>
Mysql> select * from Job;
+------+------------+
| ID | Title |
+------+------------+
| 1 | Tester |
| 2 | Accountant |
| 4 | Coder |
| 5 | Director |
| 6 | Mediator |
| 9 2 Proffessor |
| 8 | Programmer |
| 9 | Developer |
+------+------------+
8 rows in Set (0.00 sec)