CODEWITHSUNDEEP
mysqlsql
showtime
showtime

Reputation: 1462

How to delete from table A and table B (if exists) in the same query?

I have tried this below without success, it just fails because table_B is empty. So I want to at least delete the record from table_A even if table_B is empty.

FYI: table_A record always exists, so no need to search if that exists.

$sql_remove_rfid = $dbi->prepare("
DELETE 
  FROM table_A
     , table_B
  LEFT 
  JOIN table_B 
    ON table_A.id = table_B.key
WHERE table_A.id=?");

Upvotes: 0

Views: 516

Answers (2)

Nick
Nick

Reputation: 147166

Your syntax is not quite correct, it should be:

DELETE A, B
FROM table_A A
LEFT JOIN table_B B ON A.id = B.key
WHERE A.id=?

Note that in general its best to avoid using MySQL reserved words (key) as column names.

Note also that if key is a foreign key from table_B to table_A, and you always want to delete entries from table_B when the corresponding id in table_A is deleted, you can define key as a foreign key to table_A(id) and specify ON DELETE CASCADE:

ALTER TABLE table_B ADD FOREIGN KEY (`key`) REFERENCES table_A(id) ON DELETE CASCADE

Then you can simply delete from table_A and the corresponding rows in table_B (if any) will automatically be deleted:

DELETE
FROM table_A
WHERE id = ?

Upvotes: 2

George Joseph
George Joseph

Reputation: 5922

It seems you wish to delete data from TABLEA against an id regardless of whether the id exists in TABLEB. Therefore why the JOIN condition. It can be

$sql_remove_rfid = $dbi->prepare("DELETE FROM 
table_A
WHERE table_A.id=?");

Upvotes: 0

Related Questions