Is it 7 or 2000 you're using? On 2000 I've used this before...
SELECT TOP 1 *
FROM table
ORDER BY NEWID()
Double check that is is bringing back a random row as it was a while ago
that I used it. In fact I think it was on 7 and I may have used something
else.
DELETE FROM yourTable
WHERE someID <> (
SELECT TOP 1 someID
FROM table
ORDER BY NEWID()
)
That should leave you with one random row.
To answer another question I think popped up, you don't need to know the
column names and datatypes to create a temp table, or a normal(is that what
they're called) table even. SELECT INTO or INSERT SELECT (can't remember
which) will create a table if it doesn't already exist, temp tables
included(best to check this). (You might want to test the "if it doesn't
already exist" part as well).
Let us know how you get on.
Ade (another pair or parenthesis just because I can :OD)
-----Original Message-----
From: Qasim Rasheed [mailto:[EMAIL PROTECTED]
Sent: 16 August 2004 20:20
To: CF-Talk
Subject: SQL Question
Hello
I want to delete all records from a table except one (a random one).
Is there a way to do it? I am using SQL Server
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
- SQL Question Qasim Rasheed
- Re: SQL Question Greg Morphis
- Re: SQL Question Qasim Rasheed
- Re: SQL Question G
- Re: SQL Question Greg Morphis
- Re: SQL Question Qasim Rasheed
- Re: SQL Question Qasim Rasheed
- Re: SQL Question G
- Re: SQL Question Qasim Rasheed
- Re: SQL Question Ewok
- RE: SQL Question Adrian Lynch
- RE: SQL Question Tangorre, Michael
- RE: sql question Robertson-Ravo, Neil (RX)
- Re: sql question Tony Weeg
- Re: sql question Michael Traher
- RE: sql question Robertson-Ravo, Neil (RX)
- RE: sql question Jason Reichenbach
- SQL question Jeff Fongemie
- Re: SQL question Barney Boisvert
- Re: SQL question Jeff Fongemie
- SQL Question Andy Ousterhout