APM: DBI multi table delete (typo)

[Jeremy Brooks]

typo...

I meant to say:

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





On Mon, 2004-02-23 at 11:36, Jeremy Brooks wrote:
> 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
> > >