CODEWITHSUNDEEP
mysqlsql
Kanchana Randika
Kanchana Randika

Reputation: 546

MySQL delete if row exist?

I want to delete a data from multiple tables the data surely resides in the ad_master but sub table is vary from time to time. I tried following query but it at least doesn't delete a single record and keep silence. I want to delete every record associated with my given number($delete_no) if they exist on any of my given tables.

DELETE am,sub1,sub2,sub3
FROM ad_master am
INNER JOIN ad_vehicles sub1
ON sub1.Ad_no=am.Ad_no
INNER JOIN ad_properties sub2
ON sub2.Ad_no=am.Ad_no
INNER JOIN ad_electronics sub3
ON sub3.Ad_no=am.Ad_no
WHERE am.Ad_no=$delete_no

Please guide me to solve this. Thanx.

Upvotes: 1

Views: 1028

Answers (2)

user207421
user207421

Reputation: 311054

You're trying to implement referential integrity. Let the database do it, it's much better at it than you are. Ad_no should be a foreign key in all the tables except the master, referring to the master in each case, and defined so that deletes cascade. Then all you have to do is delete the master row.

Upvotes: 0

agorokhov
agorokhov

Reputation: 196

Try LEFT JOIN instead INNER. With INNER JOIN you make intersection of two tables and if $delete_no not exists in ad_vehicles the result is empty.

Upvotes: 5

Related Questions