The better way to do this would be to have the cart.prod_id be a
foreign key field that references products.id (available as of
3.23.44) and use ON CASCADE DELETE. But that alters your schema,
and you want to think very hard about the problems it might create
(and do it with your data on a test
Has everyone forgotten that since v4.0, MySQL has a multi-table delete
statement?
http://dev.mysql.com/doc/refman/4.1/en/delete.html
Your original query was almost it (Adrian was on the right track, too):
DELETE cart
FROM cart
LEFT JOIN products prod
WHERE prod.id is null
or
4.0.18-standard-log
I have a table cart and a table products
Key is as follows:
products.id = cart.prod_id
The problem I have is we have decided to store the users cart, so when they
come back it is still in the same state they left it. Pretty usual stuff so
far.
Two things can possible
IN MySQL 5 you could use a sub query(
http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html).
However, i would try using a left join between cart and products and
then bring back the results where the products.id field is 'NULL'.
There may be a better way of doing this but
