No temp table

Is it possible to rewrite this delete so that a temp table isn't used?
key_1 and date_key form the primary key. I want to remove any rows with the
same key_1 and a date_key greater than today's date.
Cheers!
declare @.today datetime
select @.today = getdate()
select key_1, date_key
into #temp
from my_table
where date_key > @.today
group by key_1 having count(*) > 1
delete my_table
from #temp, my_table m
where #temp.date_key = m.date_key
and #temp.key_1 = m.key_1Gyruss wrote:
> Is it possible to rewrite this delete so that a temp table isn't used?
> key_1 and date_key form the primary key. I want to remove any rows
> with the same key_1 and a date_key greater than today's date.
> Cheers!
> declare @.today datetime
> select @.today = getdate()
> select key_1, date_key
> into #temp
> from my_table
> where date_key > @.today
> group by key_1 having count(*) > 1
>
> delete my_table
> from #temp, my_table m
> where #temp.date_key = m.date_key
> and #temp.key_1 = m.key_1
Untested:
Delete From my_table
Where date_key > @.today
and key_1 IN (
Select key_1
From my_table
Where date_key > @.today
group by key_1 having count(*) > 1)
David Gugick
Imceda Software
www.imceda.com|||DELETE FROM My_table
WHERE EXISTS
(SELECT *
FROM My_table AS M
WHERE M.date_key = Mytable.date_key
AND M..key_1 = Mytable.key_1);
You probably want to learn SQL and stop using the dialect with the FROM
clause and joins in it. The results are unpredictable as well as not
portable.|||The SELECT statement you posted isn't legal so I'm not certain what you
intended. You also didn't tell us the primary key, which might have
helped. Do you have a key?
DELETE FROM my_table
WHERE date_key > CURRENT_TIMESTAMP
AND EXISTS
(SELECT *
FROM my_table AS M
WHERE key_1 = my_table.key_1
AND date_key <= CURRENT_TIMESTAMP)
David Portas
SQL Server MVP
--|||>> I want to remove any rows with the same key_1 and a date_key greater
than today's date <<
Opps! I read to mean that if there is a group with all members in the
future, we leave them alone. If there is a group with all members in
the past or present, we leave them alone. If there is a group with
members in the past or present or future, we delete the future members.
DELETE FROM My_table
WHERE Mytable.date_key > CURRENT_TIMESTAMP
AND EXISTS
(SELECT *
FROM My_table AS M
WHERE AND M.key_1 = Mytable.key_1
AND M.date_key <= CURRENT_TIMESTAMP );|||On Sat, 26 Feb 2005 09:50:04 +1100, Gyruss wrote:

>Is it possible to rewrite this delete so that a temp table isn't used?
>key_1 and date_key form the primary key. I want to remove any rows with th
e
>same key_1 and a date_key greater than today's date.
>Cheers!
>declare @.today datetime
>select @.today = getdate()
> select key_1, date_key
> into #temp
> from my_table
> where date_key > @.today
>group by key_1 having count(*) > 1
>
>delete my_table
> from #temp, my_table m
> where #temp.date_key = m.date_key
> and #temp.key_1 = m.key_1
>
Hi Gyruss,
I'm . Your text says "any rows with the same key_1 and a
date_key greater than todays's date". But the query says "any rows with
the same key_1 and a data_key greater than today's date, unless only one
such row exists for this key_1".
Try this one. I didn't test it, since you didn't post CREATE TABLE and
INSERT statements to test it with.
DELETE my_table
WHERE date_key > CURRENT_TIMESTAMP
AND (SELECT COUNT(*)
FROM my_table AS a
WHERE a.key_1 = my_table.key_1
AND a.date_key > CURRENT_TIMESTAMP) > 1
This mimics your query. If the text description is accurate, you can
reduce this to
DELETE my_table
WHERE date_key > CURRENT_TIMESTAMP
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)