List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 29 1999 10:53am
Subject:Re: 17.16 Deleting rows from related tables
View as plain text  
On Mi, 1999-07-28 12:01:24 -0700, Steven Jones wrote:
> After looking at the manual section
> 
>  17.16 Deleting rows from related tables
> 
> I wrote a little script to attempt to do such 
> a deletion using the "mysql" client program.

First, the approach described in 17.16 differs a little bit from what
you do in your script.  Applied to your script having a second table
which holds the ids of the rows to be deleted, you would do:

  SELECT c1 FROM t2;
  --> this yields a set if ids, which you retrieve in your application
      and store in some variable or list.  Then make a new SQL query:
  DELETE FROM t1 WHERE c1 IN ('a', 'c');

You can't do that just with pure SQL queries using the "mysql"
program, but need some host language like C, Perl, or PHP (or maybe
even the shell) to tempororary store the list of ids.

Another different approach is: don't use a seperate table to hold the
ids of rows to be deleted, but just add one more field to table t1:
  deleted ENUM('Y','N') NOT NULL DEFAULT 'N'

Then you just update this field to 'Y' if some recoed should be
deleted and later on you issue this query:
  DELETE FROM t1 WHERE deleted='Y';

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
17.16 Deleting rows from related tablesSteven Jones28 Jul
  • Re: 17.16 Deleting rows from related tablesMartin Ramsch29 Jul