i have table AAA and 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…
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