List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 8:05pm
Subject:Re: DELETE with JOINs
View as plain text  
On Tue, 1999-09-28 10:19:52 -0700, Nathan Clegg wrote:
> Is there any way to DELETE from one table based on a join with
> another table in a single statement?

With current MySQL versions: no, it's not possible with a single query.

> Any hints, or do I have to do a select, save the results, and then
> delete?

Exactly this is maybe the best way for now.

The other possibility that comes to mind is:
  CREATE TABLE tmptbl ( ...definitions_like_origtbl... );
  INSERT INTO tmptbl
    SELECT ...all_fields_of_origtbl_with_updated_values...
    FROM   origtbl, othertbl, ...
    WHERE ...;
  REPLACE INTO origtbl
    SELECT * FROM tmptbl;
  DROP TABLE tmptbl;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
DELETE with JOINsNathan Clegg28 Sep
  • Re: DELETE with JOINsTonu Samuel28 Sep
  • Re: DELETE with JOINsMartin Ramsch28 Sep
  • Re: DELETE with JOINsBob Kline28 Sep