I would love to give proper credit to the individual who created this, but
alas, I cannot remember where I stumbled upon it.
When I needed to do some cleaning, I did \T from the mysql app to write to a
file... then ran the script
SELECT CONCAT('DELETE FROM post WHERE threadid = ',
post.threadid, ';')
FROM post, threads
WHERE post.threadid = threads.tid;
It'll write out a set of delete statements (can be modified to update
statements), then just pump these newly created statements through the mysql
app.
I used this because MySQL seemed to have issues with me specifying something
like I did in SQL Server.
delete from post
from post
inner join thread t on t.threadid = p.threadid
Kelly
------------------
Kelly Firkins
[EMAIL PROTECTED]
>From: "Etienne Marcotte" <[EMAIL PROTECTED]>
>To: Harlan Feinstein <[EMAIL PROTECTED]>
>CC: "rory o'connor" <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
>Subject: Re: efficient DELETE query - 2 tables
>Date: Mon, 10 Dec 2001 08:51:56 -0500
>
>mySQL can't do subselects afaik
>
>Etienne
>
>Harlan Feinstein wrote:
> >
> > Rory> I want to write an efficient query that will delete all records
> > Rory> from one table when they show up in another table (pending
> > Rory> shipments --> shipped shipments). I can't do it with this:
> >
> > mysql> DELETE from pending_2 WHERE pending_2.ordno =
>ordersearch_2.ordno;
> >
> > Rory> because you can only refer to columns of one table in a delete
> > Rory> query. Does anybody have an effiecnt way for me to do this with
> > Rory> Perl DBI?
> >
> > This isn't really a Perl question, is it? Isn't the most efficient way
>to
> > have the DB handle all of it? Something like:
> >
> > DELETE FROM pending_2 WHERE ordno IN
> > (SELECT ordno FROM ordersearch_2)
> >
> > --Harlan
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>--
>Etienne Marcotte
>Specifications Management - Quality Control
>Imperial Tobacco Ltd. - Montreal (Qc) Canada
>514.932.6161 x.4001
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
