Thanks for the nice suggestion but my problem is that there are
several tables and some of them doesn't have any primary key
associated with them. A brute force approach that I tried and it
didn't even worked was to get all database table name from SQL Server
metadata and then loop through that query and deleting all records
except top 1. But the problem here is the program doesn't know the
column name in advance. What I did was to do a temp query and get the
column names using query.culumnlist and then delete the record as
follows
<cfquery name="temp" datasource="#db.dsn#">
select *
from #tablename#
</cfquery>
<cfquery name="del" datasource="#db.dsn#">
delete
from #tablename#
where #listfirst( temp.columnlist )# not in (select top 1
#listfirst( temp.columnlist )# from #tablename# )
</cfquery>
It works as long as listfirst() returns first column as numeric. I am
guessing there is a better and more elegant approach to this? I still
need help :(
----- Original Message -----
From: G <[EMAIL PROTECTED]>
Date: Mon, 16 Aug 2004 14:46:48 -0500
Subject: Re: SQL Question
To: CF-Talk <[EMAIL PROTECTED]>
Another option would be, if you had an auto-generated numeric primary
key, determine your range, call a random number generator, verify that
the number is a valid primary key, then do a DELETE * WHERE pk !=
randomKey.
Just thinking out loud.....
----- Original Message -----
From: Greg Morphis
To: CF-Talk
Sent: Monday, August 16, 2004 2:32 PM
Subject: Re: SQL Question
I think you'd probably have to have a temp table and save a record to it
and then delete out the entire original table and then insert the
single row back into the table from temp.
On Mon, 16 Aug 2004 15:19:39 -0400, Qasim Rasheed
<[EMAIL PROTECTED]> wrote:
> 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]
