Thanks, that did it!
> -Original Message-
> From: Peter Brawley [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 08, 2008 11:57 AM
> To: Jeff Mckeon
> Cc: mysql@lists.mysql.com
> Subject: Re: delete query question
>
> Jeff,
>
> >Table2.ticket = t
Jeff,
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..)
Like this (untested)?
DELE
> Sent: Tuesday, July 08, 2008 11:27 AM
> > To: Jeff Mckeon
> > Cc: mysql@lists.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 D
> -Original Message-
> From: Ian Simpson [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 08, 2008 11:27 AM
> To: Jeff Mckeon
> Cc: mysql@lists.mysql.com
> Subject: Re: delete query question
>
> If the tables are InnoDB, you could temporarily set up a foreign ke
If the tables are InnoDB, you could temporarily set up a foreign key
relationship between the two, with the 'ON DELETE CASCADE' option.
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..
>
> Tabl
IN() subqueries in MySQL are badly optimized. It's usually better to
use a JOIN, even though it's non-standard:
DELETE geno_260k.*
FROM geno_260k
INNER JOIN (
SELECT ident FROM geno_260k
WHERE a1=0
GROUP BY ident HAVING count(*)>25
) AS der USING(ident);
Try profili
Thanks baron,
I will try this just for test purposes as I already wrote a script, which is
slow but not as bad as using IN()
Olaf
On 9/5/07 3:29 PM, "Baron Schwartz" <[EMAIL PROTECTED]> wrote:
> IN() subqueries in MySQL are badly optimized. It's usually better to
> use a JOIN, even though it'
Thanks,
This seems to work but that IN seems to be really slow...
On 9/5/07 9:41 AM, "Justin" <[EMAIL PROTECTED]> wrote:
> try
>
> SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE
> a1=0
> GROUP BY ident HAVING count(a1)>25);
>
> This will give you what you're d
reply inline
On 9/5/07, Olaf Stein <[EMAIL PROTECTED]> wrote:
>
> delete from geno_260k where ident=(select ident from geno_260k where a1=0
> group by ident having count(a1)>25);
When a sub query returns more than one row in a where clause, then "="
should be replaced by the "in" .
--
Th
try
SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE
a1=0
GROUP BY ident HAVING count(a1)>25);
This will give you what you're deleting first.. then if that is good. do
DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE
a1=0
GROUP BY ident
10 matches
Mail list logo
