[Resolved] Please Help With SQL Query

[voidstate]

Hi

I am trying to write an SQL statement which will delete all related rows from several databases. I can't get it to work though. It seems OK as long as all the fields match up which makes me think it's something to do with the ANDs I am using.

Can anyone see what might be wrong with this:

DELETE FROM sites, 
					sitewide_content, 
					site_settings, 
					domains, 
					site_bolt_ons, 
					mailinglists, 
					mails,
					subscribers,
					   mailinglists_subscribers,
					sections,
					   all_pages,
						 dynamic_pages,
						 mailform_pages,
							mailform_fields,
						 news_pages,
							news_items,
						 mailinglist_pages 
					WHERE sites.site_id = '" . $_GET['site_id'] . "' 
					AND sitewide_content.site_id = sites.site_id 
					AND site_settings.site_id = sites.site_id 
					AND domains.site_id = sites.site_id 
					AND site_bolt_ons.site_id = sites.site_id 
					AND mailinglists.site_id = sites.site_id 
					AND mails.site_id = sites.site_id 
					AND subscribers.site_id = sites.site_id 
						AND mailinglists_subscribers.subscriber_id = subscribers.subscriber_id 
					AND sections.site_id = sites.site_id 
						AND all_pages.section_id = sections.section_id 
							AND dynamic_pages.page_id = all_pages.page_id 
							AND mailform_pages.page_id = all_pages.page_id 
								AND mailform_fields.mailform_page_id = mailform_pages.mailform_page_id 
							AND news_pages.page_id = all_pages.page_id 
								AND news_items.news_page_id = news_pages.news_page_id 
							AND mailinglist_pages.page_id = all_pages.page_id

I am getting the following error message:

Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE sites.site_id = '103' AND sitewide_content.site_id

Thanks

voidstate

[ultraslacker]

Are you running a version that supports multiple tables in a DELETE?

[tomhath]

You need to tell it which tables you want rows deleted from.

For example, to delete rows from table1 and table2, but not from table3:

DELETE table1, table2
FROM table1, table2, table3
WHERE ...

or

DELETE FROM table1, table2
USING table1, table2, table3
WHERE ...

http://dev.mysql.com/doc/mysql/en/delete.html2

[voidstate]

Thanks for your replies

I split the query up into one for each table in the end and looped through them. Not the most efficient way of doing it guess but it worked.

voidstate