Gaetano Mendola wrote:
Steven Rosenstein wrote:
DELETE FROM detail JOIN summary ON (summary.id=detail.sum_id) WHERE collect_date='2005-02-05';
You have to tell it what table you are deleting from. Select * from A join B is both tables. What you want to do is fix the where clause.
DELETE FROM detail WHERE detail.sum_id in ( select id from summary ) AND collect_date='2005-02-05';
I'm guessing this should actually be DELETE FROM detail WHERE detail.sum_id in ( SELECT id FROM summary WHERE collect_date='2005-02-05' ); Otherwise you wouldn't really need the join.
You have to come up with a plan that yields rows that are in the table you want to delete. The rows that result from select * from detail join summary, contain values from both tables.
If you want to delete from both tables, I think this has to be 2 deletes. Probably best to be in a transaction.
BEGIN; DELETE FROM detail WHERE ... DELETE FROM summary WHERE collect_date = '2005-02-05'; COMMIT;
Regards Gaetano Mendola
Description: OpenPGP digital signature