how to use select and delete in one query-CodePudding

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...

CodePudding user response：

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';

CodePudding user response：

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

);

CodePudding user response：

I found this request from your links.

DELETE AAA.*, BBB.*
   FROM AAA
        INNER JOIN BBB ON AAA.ID = BBB.AAA_ID 
  WHERE (AAA.Token)='rererere'

it works

is this request reliable?