APM: DBI multi table delete

[Jeremy Brooks]

I tried that and all the records in all the tables got deleted.
I also tried using OR instead of AND..  same thing.
Now I'm trying:

WHERE t1.username="joe"
AND   t1.username=t2.username
AND   t1.username=t3.username
AND   t1.username=t4.username
AND   t1.username=t5.username 

and it seems to work...


Could there be a problem with the database? This is Mysql 4.0.13




On Mon, 2004-02-23 at 11:12, Evan Harris wrote:
> > ----------
> > From: 	Evan Harris[SMTP:EHARRIS at PUREMAGIC.COM]
> > Sent: 	Monday, February 23, 2004 11:12:43 AM
> > To: 	Jeremy Brooks
> > Cc: 	apm
> > Subject: 	Re: APM: DBI multi table delete
> > Auto forwarded by a Rule
> > 
> > 
> Your problem is probably that you need to fully specify the table fields.
> Try:
> 
> DELETE FROM t1,t2,t3,t4,t5 WHERE t1.username = 'joe' OR t2.usernam = 'joe'
> OR t3.username = 'joe' OR t4.username = 'joe' OR t5.username = 'joe'
> 
> I've never done multitable delets, so I don't know if that even works, but
> if it does, the above should too, and should do what you want.
> 
> A sure way to do it is to do a seperate delete statement for each table.
> 
> Evan
> 
> 
> On 23 Feb 2004, Jeremy Brooks wrote:
> 
> >
> > I need to quickly come up with a sql statement to run from a perl/DBI
> > script that deletes all records from several tables that have a
> > particular username, 'joe', in the username column.  I know this is
> > probably simple, but what I've done so far hasn't worked as expected.  I
> > think that these are the correct DELETE and FROM clauses:
> >
> > DELETE t1,t2,t3,t4,t5 FROM t1,t2,t3,t4,t5
> >
> > But, I'm not sure how to write the condition that determines what
> > records to delete.  let's say I want to delete any record from all of
> > the tables that have a username value of 'joe'.
> >
> > Can some please show me exactly what the sql statement ought to be?
> >
> > thank in advance!
> > -Jeremy
> >
> >
> >
> > _______________________________________________
> > Austin mailing list
> > Austin at mail.pm.org
> > http://mail.pm.org/mailman/listinfo/austin
> >