Multiple delete but value optional so not always there

[bennystylee]

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

'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

'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

[Phill Pafford]

I don't think you can delete from multiple tables like this.

I have used UNION in the past to run one query but multiple queries at once.

Try something like this (Haven't tested this)

DELETE journal
FROM journal
WHERE quotes_id = $jnl_id'
UNION
DELETE image
FROM image
WHERE quotes_id = $jnl_id'
UNION
DELETE other_image
FROM other_image
WHERE quotes_id = $jnl_id'
UNION
DELETE quotes
FROM quotes
WHERE quotes_id = $jnl_id'
UNION
DELETE quotes_two
FROM quotes_two
WHERE quotes_id = $jnl_id'
UNION
DELETE tags
FROM tags
WHERE quotes_id = $jnl_id'
UNION
DELETE youtube
FROM youtube
WHERE quotes_id = $jnl_id'


Now I just guessed at the table names and fields from your query above so please change it as you need to. Also if multiple fields are in the same table, see example below.


DELETE journal
FROM journal
WHERE quotes_id = $jnl_id'
UNION
DELETE image, other_image
FROM image
WHERE quotes_id = $jnl_id'
UNION
DELETE quotes, quotes_two
FROM quotes
WHERE quotes_id = $jnl_id'
UNION
DELETE tags
FROM tags
WHERE quotes_id = $jnl_id'
UNION
DELETE youtube
FROM youtube
WHERE quotes_id = $jnl_id'


Hope this helps

[bennystylee]

I have had a little look at this with another query - it seems i cant do this as it throws an sql syntax error. SELECTS with union no problems, DELETE with unions doesnt seem to work.... and searching around apparently its not possible (that was a 2007 post) php5 and mysql 5.1 here

There isnt a lot around on "DELETE and UNION" on search engines or my books so my presumption is it doesnt work. Shame as I switched to using Innodb and delete cascade - then have decided (because i am using COUNT(*) alot) to go back to MYISAM tables and 8 seperate DELETE queries deleting from these differnet tables (where the value may or may not be there) using the same value - seems very inefficient to me. And I want to avoid doing any big leftjoins as the 8 tables will all together be holding quite a bit of information so i can see this slowing it down

hmm........an advise oh wise internet of a thousand million brains?