Page 1 of 1

DELETE FROM more than on table

Posted: Mon Nov 01, 2004 1:37 pm
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

Posted: Mon Nov 01, 2004 1:57 pm
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

Posted: Mon Nov 01, 2004 2:51 pm
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.

Posted: Wed Nov 03, 2004 7:40 am
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

Posted: Wed Nov 03, 2004 8:39 am
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