MySQL delete if row exist?

Question

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.

agorokhov · Accepted Answer · 2013-01-28 23:36:23Z

5

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.

answered Jan 28, 2013 at 23:36

agorokhov

1961 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kanchana Randika Over a year ago

It's great @agorokhov. When I change INNER with LEFT it deletes everything as I wanted. Thanx a lot.

user207421 · Accepted Answer · 2013-01-28 23:47:35Z

0

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.

answered Jan 28, 2013 at 23:47

user207421

312k45 gold badges323 silver badges493 bronze badges

Collectives™ on Stack Overflow

MySQL delete if row exist?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related