From: Ian Simpson (iansmyjobgroup.co.uk)
Date: Tue Jul 08 2008 - 10:50:47 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Oh well ;)

It looks like you can use joins in a delete statement, and delete the
joined rows, which will delete from the individual tables.

So something like:

delete table1, table2 from table1 inner join table2 on table1.ID =
table2.ticket where...

should do it

I modified the above code from

http://dev.mysql.com/doc/refman/5.0/en/delete.html

just search in the page for 'join' and you'll find the relevant section

On Tue, 2008-07-08 at 11:35 -0400, Jeff Mckeon wrote:
>
> > -----Original Message-----
> > From: Ian Simpson [mailto:iansmyjobgroup.co.uk]
> > Sent: Tuesday, July 08, 2008 11:27 AM
> > To: Jeff Mckeon
> > Cc: mysqllists.mysql.com
> > Subject: Re: delete query question
> >
> > If the tables are InnoDB, you could temporarily set up a foreign key
> > relationship between the two, with the 'ON DELETE CASCADE' option.
> >
>
> Nope, MyISAM...
>
> > On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote:
> > > I think this is possible but I'm having a total brain fart as to how
> > to
> > > construct the query..
> > >
> > > Table2.ticket = table1.ID
> > >
> > > Table2 is a many to 1 relationship to table1
> > >
> > > I need to delete all records from table1 where created <
> > > unix_timestamp(date_sub(now(), interval 3 month))
> > > And all rows from table2 where Table2.ticket = Table1.ID (of the
> > deleted
> > > rows..)
> > >
> > > Can't this be done in one query? Or two?
> > >
> > > Thanks,
> > >
> > > Jeff
> > >
> > >
> > >
> > >
> > --
> > Ian Simpson
> > System Administrator
> > MyJobGroup
> >
> > This email may contain confidential information and is intended for the
> > recipient(s) only. If an addressing or transmission error has
> > misdirected this email, please notify the author by replying to this
> > email. If you are not the intended recipient(s) disclosure,
> > distribution, copying or printing of this email is strictly prohibited
> > and you should destroy this mail. Information or opinions in this
> > message shall not be treated as neither given nor endorsed by the
> > company. Neither the company nor the sender accepts any responsibility
> > for viruses or other destructive elements and it is your responsibility
> > to scan any attachments.
>
>
--
Ian Simpson
System Administrator
MyJobGroup

This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]