Sqldelete statement and how to delete multiple table data at the same time

Sqldelete statement and multitable data deletion method delete & nbsp; [from] & nbsp; & nbsp; {table_namewith (& lt; table_hint_limited & gt ;[... n]) & nbsp; & SQL delete statement and how to delete multiple table data simultaneously
Delete
[From]
{Table_name with (<table_hint_limited> [... n])
| View_name
| Rowset_function_limited
}

[From {<table_source>} [,... n]

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

From

Is an optional keyword. It 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.

The table variable in its scope, or the table name (or view name) composed of four parts using the opendatasource function as the server name, can also be used as the table source in the delete statement.

With ( [... N])

Specify one or more table prompts allowed by the target table. The with keyword and parentheses are required. Readpast, nolock, and readuncommitted are not allowed. More information about table prompts

Multi-Table relational SQL deletion statement

> Create table employee (
-> Id int,
-> First_name varchar (15 ),
-> Last_name varchar (15 ),
-> Start_date date,
-> End_date date,
-> Salary float (8, 2 ),
-> City varchar (10 ),
-> Description varchar (15)
-> );
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', '000000', '000000', 19960725, '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', '000000', '000000', 19760321, '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', '000000', '000000', 19781212, '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, 'cela', 'Rice ', '000000', '000000', 19821024, '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', '000000', '000000', 19840115, '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', '000000', '000000', 19870730, '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', '123', '123', 19901231, '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', '000000', '000000', 19960917, '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, 'ctor ctor ');
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, 'grammer ');
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> * from job;
+ ------ + ------------ +
| Id | title |
+ ------ + ------------ +
| 1 | tester |
| 2 | accountant |
| 3 | developer |
| 4 | coder |
| 5 | director |
| 6 | mediator |
| 7 | 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 | 1234.56 | toronto | programmer |
| 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester |
| 3 | james | smith | 6544.78 | vancouver | tester |
| 4 | celia | rice | 1982-10-24 | 2344.78 | vancouver | manager |
| 5 | robert | black | 2334.78 | vancouver | tester |
| 6 | linda | green | 4322.78 | new york | tester |
| 7 | david | larry | 7897.78 | new york | manager |
| 8 | james | cat | 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 | 1234.56 | toronto | programmer |
| 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester |
| 4 | celia | rice | 1982-10-24 | 2344.78 | vancouver | manager |
| 5 | robert | black | 2334.78 | vancouver | tester |
| 6 | linda | green | 4322.78 | new york | tester |
| 7 | david | larry | 7897.78 | new york | manager |
| 8 | james | cat | 1232.78 | vancouver | tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
7 rows in set (0.00 sec)

Method 2

Mysql> delete AB
-> From authorbook as AB, authors as
-> 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 | 1234.56 | toronto | programmer |
| 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester |
| 4 | celia | rice | 1982-10-24 | 2344.78 | vancouver | manager |
| 5 | robert | black | 2334.78 | vancouver | tester |
| 6 | linda | green | 4322.78 | new york | tester |
| 7 | david | larry | 7897.78 | new york | manager |
| 8 | james | cat | 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 |
| 7 | proffessor |
| 8 | programmer |
| 9 | developer |
+ ------ + ------------ +
8 rows in set (0.00 sec)