DELETE FROM more than on table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

DELETE FROM more than on table

Post by kendall »

Hello,

The following is a query string i use to delete a row from a table aswell as delete related rows from other tables

Code: Select all

DELETE products.*, product_options.*, price_options.* FROM products, product_options, price_options WHERE price_options.ProductID = products.ProductID AND product_options.ProductID = products.ProductID AND  products.ProductID = '%s'
thing is it doesnt even delete the row from the main table.

Can i delete from more than one table in a single query?

Kendall
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

I was having trouble accessing the search functions earlier so i didnt see the post reference viewtopic.php?t=23774&highlight=delete+one+table

I havent tried it yet but i am going to but can someone explain why i need to have the tables in alias to delete related rows in the tables

Kendall
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

kendall wrote: I havent tried it yet but i am going to but can someone explain why i need to have the tables in alias to delete related rows in the tables
Replied there.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Ok Wierd....your explanation was a bit unclear but i am experimenting with the left join (argh! these left joins) but note that i am trying to delete related information from more than 2 tables. I dont understand
categories.id where categories.id is null
how does the categories is null fit in to all of this as i am trying to delete only related data?

Kendall
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Ok guys i used this query to delete related information ONLY from multipule tables

Code: Select all

DELETE FROM products, price_options, product_options USING products, price_options, product_options WHERE product_options.ProductID = products.ProductID AND price_options.ProductID = products.ProductID AND products.ProductID = 'test'
Kendall
Post Reply