how to use select and delete in one query

Issue

i have table AAA and BBB

table AAA

table BBB

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…

Solution

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

Answered By – Tim Biegeleisen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0