List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 10 1999 9:25pm
Subject:Re: DELETE SYNTAX
View as plain text  
On Sun, 1999-10-10 15:48:02 +1000, Michael Farr wrote:
> Is there any way to do a query like this one without a temporary table
> 
> DELETE FROM DentalScheme d LEFT JOIN Patient p ON p.patientId =
> d.patientId WHERE ISNULL(p.patientId);

There are several work-arounds I know of:

a) First get a list of all rows to delete, then delete them.

     SELECT d.id
       FROM DentalScheme AS d LEFT JOIN
            Patient      AS p ON d.patientId = p.patientId
      WHERE ISNULL(p.patientId);
     /* now store the list of ids on the application side
        and build a new delete statement */
     DELETE FROM DentalScheme WHERE id IN (...list_of_ids...);

   This is what I'd recommend in most cases!

b) Copy everything over to a temporary table, except the rows to be
   deleted, and then replace the old table with the newly created one:

     CREATE TABLE DentalScheme2 (
       ...same field definitions as in DentalScheme...
     );
     INSERT INTO DentalScheme2
         SELECT d.*
           FROM DentalScheme AS d LEFT JOIN
                Patient      AS p ON d.patientId = p.patientId
          WHERE NOT ISNULL(p.patientId); /* NOT! */
     DROP TABLE DentalScheme;
     ALTER TABLE DentalScheme2 RENAME DentalScheme;

   I'm not entirely sure if it might be necessary to use FLUSH TABLES
   before and after the ALTER command ...
   And if there could be conflicts with concurrently running queries,
   you also will need locks:

     CREATE TABLE DentalScheme2 (
       ...same field definitions as in DentalScheme...
     );
     LOCK TABLES DentalScheme AS d WRITE
               , DentalScheme      WRITE
               , DentalScheme2     WRITE;
     DELETE FROM DentalScheme2;
     INSERT INTO DentalScheme2
         SELECT d.*
           FROM DentalScheme AS d LEFT JOIN
                Patient      AS p ON d.patientId = p.patientId
          WHERE NOT ISNULL(p.patientId); /* NOT! */
     DROP TABLE DentalScheme;
     FLUSH TABLES;
     ALTER TABLE DentalScheme2 RENAME DentalScheme;
     FLUSH TABLES;
     UNLOCK TABLES;

c) Add an additional column status to mark the rows to be deleted,
   update this column using a temporary table and REPLACE INTO,
   and then delete the rows with status 'deleted':

     ALTER TABLE DentalScheme
       ADD COLUMN status ENUM('deleted') DEFAULT '' NOT NULL;

     CREATE TABLE DentalScheme2 (
       ...same field definitions as in DentalScheme...
     );
     INSERT INTO DentalScheme2
         SELECT ...all fields of DentalScheme except status...
                , 'deleted'
           FROM DentalScheme AS d LEFT JOIN
                Patient      AS p ON d.patientId = p.patientId
          WHERE ISNULL(p.patientId);
     REPLACE INTO DentalScheme SELECT * FROM DentalScheme2;

     DELETE FROM DentalScheme WHERE status='deleted';

Method c) also show how to do updates which need a join.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
Thread
DELETE SYNTAXMichael Farr10 Oct
  • Re: DELETE SYNTAXsinisa10 Oct
  • Re: DELETE SYNTAXMartin Ramsch10 Oct
    • Re: DELETE SYNTAXThimble Smith10 Oct
    • Re: DELETE SYNTAXMichael Farr11 Oct
      • Re: DELETE SYNTAXBenjamin Pflugmann11 Oct
        • Re: DELETE SYNTAXMichael Farr11 Oct
          • Re: DELETE SYNTAXBenjamin Pflugmann11 Oct