how to use select and delete in one query

Question

i have table AAA and BBB

table AAA

ID	Name	Token
1	Anna	dwdwdwdd
2	Bob	rererere
3	Cindy	gfgfgfgf

table BBB

ID	AAA_ID
5	1
6	2
7	3

How can I delete from two tables in one query

for example, I need to delete from the AAA table where the token = rererere and at the same time delete AAA_ID from the BBB table

how can i do this please help...

Create FOREIGN KEY with ON DELETE CASCADE. Then simply delete from main table - and related rows from slave table will be deleted automatically. — Akina, Aug 18 '22 at 05:09
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=baaa1bdb519193bfad4d4225cf2159e1 — Akina, Aug 18 '22 at 05:14
@ysth: Oh sorry, let me edit my comment. For MS SQL Server, It seems that You cannot `DELETE` from multiple tables with a single expression. Or you have to use a TRIGGER. See: https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a — The Anh Nguyen, Aug 19 '22 at 09:31

score 1 · Accepted Answer · answered Aug 18 '22 at 05:10

Perhaps the best way to handle this would be to use cascading deletion. Consider the following schema definition:

CREATE TABLE AAA (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    Token VARCHAR(255) NOT NULL,
);

CREATE TABLE BBB (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    AAA_ID INT NOT NULL,
    FOREIGN KEY (AAA_ID) REFERENCES AAA (ID) ON DELETE CASCADE
);

Using the above schema, deleting a record from the AAA table will automatically cause any records in the BBB table which are linked via the AAA_ID foreign key to also be deleted.

Actually, on MySQL you can delete from two or more tables at once, using delete join syntax. So the following query should be able to achieve the same thing as cascading deletion, if you can't make the schema change for some reason.

DELETE a, b     -- specify both aliases to target both AAA and BBB
FROM AAA a
INNER JOIN BBB b
    ON b.AAA_ID = a.ID
WHERE a.Token = 'rererere';

score 0 · Answer 2 · answered Aug 18 '22 at 05:11

ON DELETE CASCADE constraint is used in MySQL to delete the rows from the child table automatically, when the rows from the parent table are deleted. For example when a student registers in an online learning platform, then all the details of the student are recorded with their unique number/id.

example to create foreign key with cascade in mysql:

CREATE TABLE Enroll (
sno INT,
cno INT,
jdate date,
PRIMARY KEY(sno,cno),
FOREIGN KEY(sno) 
    REFERENCES Student(sno)
    ON DELETE CASCADE
FOREIGN KEY(cno) 
    REFERENCES Course(cno)
    ON DELETE CASCADE

);

how to use select and delete in one query

2 Answers2