Page 1 of 1

Multiple delete but value optional so not always there

Posted: Sun Feb 01, 2009 12:13 pm
by chidge
Hi all

I currently have a number of tables for a journal.
I have one table with all the information (article, author, creation date, image folder etc)another with main image caption and filename, another with other images and captions and another for tags and another for quote one and another for quote two and another for YouTube video.

So 7 tables with different objects in. The main table with journal info will always have information in but all the other tables are optional. What I have done is use the journal id from table one as a foreign key in all the other tables.

So the problem or well the feeling that I am doing something wrong is currently to have 7 separate sql statements to delete from each table if an object is there

Code: Select all

 
'DELETE FROM quotes
                                            
WHERE quotes_id = $jnl_id'
 
But I have a feeling that this is possible in a lot less statements.
Can/Should I be searching for the count(*) in these tables and then deleting if something is there? Or should I do an “if”, with a selection sub query? please note the problem I think I am having is that the items aren’t always there they are optional

I’m a bit unsure as to why I can’t

Code: Select all

 
'DELETE journal, image, other_image, quotes, quotes_two, tags, youtube
FROM journal, image, other_image, quotes, quotes_two, tags, youtube
WHERE quotes_id = $jnl_id'
 
Also I am Myisam and realise that Innodb would enable the whole foreign key thing but I want these tables to be fully text searchable and want the speed from MyIsam...

As usual any advice is really welcome, and my word I had no idea how big mysql can be!

Thanks

Re: Multiple delete but value optional so not always there

Posted: Sun Feb 01, 2009 3:24 pm
by VladSun
Two ways:
- use triggers to automate the delete operations;
- use stored procedure to delete all content related to the item;

Also, you should use transactions in order to have data rolled back if something goes wrong.

Re: Multiple delete but value optional so not always there

Posted: Mon Feb 02, 2009 5:55 am
by chidge
thanks for the feedback I decided to go with this in the end

http://www.codingforums.com/showthread. ... post776780

as there didnt seem to be an issue with speed between innodb and mysiam