deleting records older than 180 days

I have table with over 1 billion rows, which I'd like to purge by deleting
all records older than 180 days from the current date (using the getdate()
function). Any help here would be great. Thanks."Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.
If you have a datetime stamp in the table, then this is relatively simple.
DELETE <table name>
WHERE DATEDIFF( dd, <datetime column name> , GETDATE() ) > 180
If you don't have a datetime stamp in the table, then maybe one of your
related tables can give this information.
If you supply the DDL for your table and some sample data, we can help you
out with a better query.
See the following link for more information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA|||Well Rob, I'm assuming that you have a date field that you can use to
reference?
Assuming you have, the process is quiet simple, but depending on how many
rows you are trying to delete in one go, you may have problems with your
log files.
The syntax would be something like,
delete from table where YOURdatetimefield < (dd, -180, getdate())
Like i say, you should keep an eye on your log space and also, the first
time you run this process could take a while.
Immy
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||excuse the missing sytax...
delete from table where YOURdatetimefield < dateadd(d, -180, getdate())
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uvH7FD8OGHA.3944@.tk2msftngp13.phx.gbl...
> Well Rob, I'm assuming that you have a date field that you can use to
> reference?
> Assuming you have, the process is quiet simple, but depending on how many
> rows you are trying to delete in one go, you may have problems with your
> log files.
> The syntax would be something like,
>
> Like i say, you should keep an eye on your log space and also, the first
> time you run this process could take a while.
> Immy
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>|||Hope you have a date field in your table...
In that case, try
select * from tablename where DATEDIFF(day,DATEfield,getdate())>=180
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Rob" wrote:

> I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||In addition to the other replies about how to implement the date comparison,
keep the following in mind:
For performance reasons, you will probably want to delete the rows in blocks
of 10,000 rather than one large transactions.
http://groups.google.com/group/micr...br />
22014423
Rather than purging the data from your database, you may want to migrate the
data to another table and then join it using a partitioned view.
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||>For performance reasons, you will probably want to delete the rows in blocks
>of 10,000 rather than one large transactions.
>http://groups.google.com/group/micr...201442
3
Good point, JT.
When I have been faced with this sort of massive purge of old data in
the past I found it easiest to process a single day at a time, in a
loop, oldest to newest. This keeps the rows per DELETE down to a very
conservative number. It also allowed me to add a pause in there
(WAITFOR DELAY) to let the system do other things, and the log purge
(this was using what is now called the simple recovery model, where
logs are truncated). This approach also let me interupt it and
restart it later if I had to, since it was written to start with the
oldest date currently in the file, and cancelling only wasted the
current "day" it was working on.
Roy Harvey
Beacon Falls, CT|||Thanks JT.
Through several responses to my post, I was able to construct and parse the
appropriate delete stmt. I found your suggestion of deleting in batches to b
e
recommendable. However, I'm struggling with putting a viable criteria for th
e
WHILE clause in order for the loop to start and NOT continue infinitely.
Thanks again.
"JT" wrote:

> In addition to the other replies about how to implement the date compariso
n,
> keep the following in mind:
> For performance reasons, you will probably want to delete the rows in bloc
ks
> of 10,000 rather than one large transactions.
> http://groups.google.com/group/micr... />
6922014423
> Rather than purging the data from your database, you may want to migrate t
he
> data to another table and then join it using a partitioned view.
> http://www.microsoft.com/technet/pr.../2005/spdw.mspx
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>
>|||Hi Rob,
In the example below, the WHERE clause would simply need to reference the
DateEntered column. The "set rowcount 1000" statement limits each iteration
to 1000 or fewer rows, and what prevents an infinite loop is the statement
"if @.@.rowcount = 0 break". When no more rows are available for deletion,
@.@.rowcount will be 0 and the loop will be terminated with the "break"
statement.
set rowcount 1000
while
delete from mytable where DateEntered < '2005/10/22'
if @.@.rowcount = 0 break
checkpoint
end
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:EE27EBFD-428E-465F-9EC1-CA5E03700458@.microsoft.com...
> Thanks JT.
> Through several responses to my post, I was able to construct and parse
> the
> appropriate delete stmt. I found your suggestion of deleting in batches to
> be
> recommendable. However, I'm struggling with putting a viable criteria for
> the
> WHILE clause in order for the loop to start and NOT continue infinitely.
> Thanks again.
> "JT" wrote:
>sql

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Deleting older backup files - Not Happening

I have a job that backs up all my databases. The problems
is that I have it set to delete .BAK files older than 3
days, but it is not. Does anyone know why this could be?
If you have any suggestions please let me know.
Thanks.Make sure the SQL Agent login has the correct permissions on the files.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> I have a job that backs up all my databases. The problems
> is that I have it set to delete .BAK files older than 3
> days, but it is not. Does anyone know why this could be?
> If you have any suggestions please let me know.
> Thanks.|||By the time the delete starts, it has not been 3 days
yet ?. If this is scheduled through the maintenance plan
and your database backup is taking shorter time than
before, it will not delete the old backup files. You may
want to delete the files manually or setup a batch job (or
separate cmd job) to delete it......
>--Original Message--
>I have a job that backs up all my databases. The
problems
>is that I have it set to delete .BAK files older than 3
>days, but it is not. Does anyone know why this could
be?
>If you have any suggestions please let me know.
>Thanks.
>.
>|||Shawn
Looks like you are using database maintenance plan.
Check if you set 'attempt to repaire any minor problems' ,sometime it
causes to SQL Server to be set with single user mode.
"Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> I have a job that backs up all my databases. The problems
> is that I have it set to delete .BAK files older than 3
> days, but it is not. Does anyone know why this could be?
> If you have any suggestions please let me know.
> Thanks.|||This is very helpful article
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans
.asp
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#GjdvLuzDHA.2412@.TK2MSFTNGP10.phx.gbl...
> Shawn
> Looks like you are using database maintenance plan.
> Check if you set 'attempt to repaire any minor problems' ,sometime it
> causes to SQL Server to be set with single user mode.
>
>
> "Shawn Ferguson" <sfergus2@.cscc.edu> wrote in message
> news:0a7d01c3cedd$85e94f20$a101280a@.phx.gbl...
> > I have a job that backs up all my databases. The problems
> > is that I have it set to delete .BAK files older than 3
> > days, but it is not. Does anyone know why this could be?
> > If you have any suggestions please let me know.
> >
> > Thanks.
>

deleting old backups/trn files.

i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.

Anyone have ny problems with maintenance plans not cleaning up when told?

ano because I do not use them. I create my own jobs.|||Maintenance plans are nothing but wizards that create SQL Agent jobs. If the job is modified, the maintenance plan has no clue that anything has changed, and if the maintenance plan is re-edited it will overwrite any other changes made to the jobs.
Maintenance plans are top candidates for the most confusing and misleading functionality within SQL server. I avoid them, except as a means of defining groups of databases for administrative purposes.
Open up the job in SQL Agent and check the code that is being run. It should be something like "EXEC xp_sqlmaint '-PlanID 02A52657-D546-11D1-9D8A-00A0C9054212...".
Post it here.|||here you go...

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 2A998DBD-3F5D-4685-ACCA-70354638C8C5 -Rpt "D:\mssql\REPORTS\live DB Maintenance4.txt" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia DISK -BkUpDB "D:\mssql\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt "BAK"'|||There is an additional parameter for specifying deletion of reports, and for some reason this parameter is left out of the sql_maint documentation in Books Online. I am thinking it is "-DelRpts 4WEEKS", but I am not sure. I will look it up for you once I get back to my office.

Deleting old backups

Hi,
I'd like to set up a job SQL Server agent which will delete backup files
older than several months once a backup has succeeded. How can I write a
step to do this?
Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
(them)
As you see I have no idea as to where I begin with this. Can it be done?
Thanks very much for any ideas on this
AntOn Mar 9, 2:11 am, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I'd like to set up a job SQL Server agent which will delete backup files
> older than several months once a backup has succeeded. How can I write a
> step to do this?
> Bascially, it will be: if *.BAK > dateadd(m, -2, getdate()) then delete
> (them)
> As you see I have no idea as to where I begin with this. Can it be done?
> Thanks very much for any ideas on this
> Ant
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html

deleting backups older than X days?

Hello,
I've written a number of scripts for custom maintenance and backups on our
server, the last problem I have is I don't know how to delete backup files o
lder
than x amount of days. Can anyone tell me how to do this?
Thanks,
Craig.Why don't you use database maintenance plans? They do this (delete old
files) for you...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
> Hello,
> I've written a number of scripts for custom maintenance and backups on our
> server, the last problem I have is I don't know how to delete backup files
older
> than x amount of days. Can anyone tell me how to do this?
> Thanks,
> Craig.|||If you have a look at the delete code in this proc it should give you the
right idea
http://www.sql-server-performance.c...sp?TOPIC_ID=864
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
> Hello,
> I've written a number of scripts for custom maintenance and backups on our
> server, the last problem I have is I don't know how to delete backup files
older
> than x amount of days. Can anyone tell me how to do this?
> Thanks,
> Craig.|||Which should cause you to go stright for the Maint. Plan Wizard
Neil MacMurchy
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OwfUuAUBEHA.1236@.TK2MSFTNGP11.phx.gbl...
> If you have a look at the delete code in this proc it should give you the
> right idea
> http://www.sql-server-performance.c...sp?TOPIC_ID=864
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
> news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
our
files
> older
>|||It's not pretty is it, what was I thinking :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil MacMurchy" <neilmcse@.hotmail.com> wrote in message
news:%23CzFYMUBEHA.1700@.TK2MSFTNGP12.phx.gbl...
> Which should cause you to go stright for the Maint. Plan Wizard
> Neil MacMurchy
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OwfUuAUBEHA.1236@.TK2MSFTNGP11.phx.gbl...
the
> our
> files
>|||Thanks for that link Jasper... but I think I'll stick with the maintenance p
lan
wizard for backups.
Jasper Smith wrote:

> If you have a look at the delete code in this proc it should give you the
> right idea
> http://www.sql-server-performance.c...sp?TOPIC_ID=864
>|||I KNEW IT! I must be an Oracle (ooooo bad pun....)
;-)
Neil
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:eFrTuvUBEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Thanks for that link Jasper... but I think I'll stick with the maintenance
plan
> wizard for backups.
>
> Jasper Smith wrote:
>
the|||It does look scary but rest assured we have been running this in production
for a long time and its got tens of thousands of backups under its belt. It
is getting to be a bit of a monster procedure though :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:eFrTuvUBEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Thanks for that link Jasper... but I think I'll stick with the maintenance
plan
> wizard for backups.
>
> Jasper Smith wrote:
>
the

Deleting backup files older than 5 days old.

I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.

I currently have E:\MSSQL.1\MSSQL\Backup\databasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bakIt looks like 2005 will handle this after the 2/07 patch is applied but until then I would like to handle this with a file task if possible. Thanks.|||

The FOREACH Loop container can enumerator the files in a directory, and takes wildcard expressions for the files to loop through. You can set it store each individual filename in a variable as it goes through the loop, and include a file task inside the loop to delete each file.

|||Thanks, jwelsh. Worked like a dream. I just loop through and delete anything older than 5 days old.

Deleting a record older then 10 minutes

Hey Guys,
I have been trying to work out how I would delete a record that was created more then 10 minutes ago.
I can use this to delete records older then a day.
DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1
Just need something now that will do it for just 10 minutes.
Cheers.You could use this:
DELETE FROM DownloadQueue
WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < DATEADD(mi,-10,GETDATE())

Delete records older than a certain period from the subscriber

Hi,
I am struggling with this for some time now:
I want all records older than a certain period (eg. one month) to be
deleted from the subscriber's database.
I have created sample database with only one table and one datetime
column, just for testing this. The table is filtered:
SELECT <published_columns> FROM [dbo].[table] WHERE DateField >=
DateAdd(month,-1,GetDate()) - so subscriber should only have records
entered last month.
So, if subscriber enters one record in the subscription db with
today's date and synchronizes immediatelly, record will remain in the
subscriber's database which is OK, but I want this record to be
removed from the subscriber's database when user will synchronize
someday in the future and this record will be older than a month.
However, this does not happen. I know that record won't be sent to the
publisher as a part of merge replication, if it was not changed
between synchronizations. For that reason, an update to the same value
is always performed on the subscriber's table before the
synchronization, eg. update table set datefield = datefield.
I can see in the merge agent history that this update is sent to the
publisher, but record still remains in the subscriber's db. It seems
to me that filter is not evaluated correctly or not evaluated at all.
If I specify reinitialization on the subscription, the record is
removed from the subscribers database, but I do not want to
reinitialize at each sync.
I have read numerous posts and noticed that this scenario should
work?!
Any idea what might be wrong?
I am using SQL 2000 with SP3.
Janez
I think you will have to run a job on the subscriber which will delete rows
which are older than a month.
The merge filter only filters modified/deleted/inserted rows. Not rows which
are not touched.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez" <janezcas@.yahoo.com> wrote in message
news:c697ea9c.0412080703.1135f892@.posting.google.c om...
> Hi,
> I am struggling with this for some time now:
> I want all records older than a certain period (eg. one month) to be
> deleted from the subscriber's database.
> I have created sample database with only one table and one datetime
> column, just for testing this. The table is filtered:
> SELECT <published_columns> FROM [dbo].[table] WHERE DateField >=
> DateAdd(month,-1,GetDate()) - so subscriber should only have records
> entered last month.
> So, if subscriber enters one record in the subscription db with
> today's date and synchronizes immediatelly, record will remain in the
> subscriber's database which is OK, but I want this record to be
> removed from the subscriber's database when user will synchronize
> someday in the future and this record will be older than a month.
> However, this does not happen. I know that record won't be sent to the
> publisher as a part of merge replication, if it was not changed
> between synchronizations. For that reason, an update to the same value
> is always performed on the subscriber's table before the
> synchronization, eg. update table set datefield = datefield.
> I can see in the merge agent history that this update is sent to the
> publisher, but record still remains in the subscriber's db. It seems
> to me that filter is not evaluated correctly or not evaluated at all.
> If I specify reinitialization on the subscription, the record is
> removed from the subscribers database, but I do not want to
> reinitialize at each sync.
> I have read numerous posts and noticed that this scenario should
> work?!
> Any idea what might be wrong?
> I am using SQL 2000 with SP3.
> Janez
|||Hi Hilary,
thanks for your response.
About your suggestion: I have thought about this too, but I believe that
this would delete the records also from the publisher at next sync which
is not what I want. I want only one month old records in the
subscriber's database, but publisher should have all records not just
one month old.
Another thing:
What do you mean by 'not touched'?
I always update records at the subscriber before each sync with the
dummy update of the date field to the same value, but the filter is not
evaluated.
Should this dummy update be enough to trigger filter evaluation?!
Regards
Janez
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||For that form of logic I normally use bi-directional transactional
replication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez Cas" <janezcas@.yahoo.com> wrote in message
news:eUUJG8U3EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for your response.
> About your suggestion: I have thought about this too, but I believe that
> this would delete the records also from the publisher at next sync which
> is not what I want. I want only one month old records in the
> subscriber's database, but publisher should have all records not just
> one month old.
> Another thing:
> What do you mean by 'not touched'?
> I always update records at the subscriber before each sync with the
> dummy update of the date field to the same value, but the filter is not
> evaluated.
> Should this dummy update be enough to trigger filter evaluation?!
> Regards
> Janez
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Delete Question

Hi:
I have conttructed the following query to delete all records from a table if
a Reservation date is older than the current date:
Delete From ConfRoomSchedule
Where DateReserved < GetDate
The syntax checks out fine but when I run it, I get an error stating that
GetDate is not a valid column.
What am I doing wrong?
Thanks
BrennanPlease change the query to following:
Delete From ConfRoomSchedule
Where DateReserved < GetDate()
"Brennan" wrote:

> Hi:
> I have conttructed the following query to delete all records from a table
if
> a Reservation date is older than the current date:
> Delete From ConfRoomSchedule
> Where DateReserved < GetDate
> The syntax checks out fine but when I run it, I get an error stating that
> GetDate is not a valid column.
> What am I doing wrong?
> Thanks
> Brennan|||Thank You
"Absar Ahmad" wrote:
> Please change the query to following:
> Delete From ConfRoomSchedule
> Where DateReserved < GetDate()
> "Brennan" wrote:
>|||I hope you are aware that getdate() will include the current time also. Thus
if you run the statement at 6:00 PM on a day, this will also delete the rows
inserted before 6:00 PM on that day.
You can use any of the following queries to get Current Date with 00:00:00
Hours as the time:
select convert(datetime,convert(varchar(10),get
date(),103),103)
select dateadd(d,floor(convert(float,getdate(),
103)),'19000101')
"Brennan" wrote:
> Thank You
> "Absar Ahmad" wrote:
>|||Its a function:
getdate()
MC
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:CDD9834F-ABE0-4047-AA9C-99856370AA7D@.microsoft.com...
> Hi:
> I have conttructed the following query to delete all records from a table
> if
> a Reservation date is older than the current date:
> Delete From ConfRoomSchedule
> Where DateReserved < GetDate
> The syntax checks out fine but when I run it, I get an error stating that
> GetDate is not a valid column.
> What am I doing wrong?
> Thanks
> Brennan

Delete older entries with duplicate names

Assume I have the following table.

id name
-- ----
1 John
2 Josh
3 Mike
4 John
5 Dana
6 Josh
7 John

I want to delete the older entries of the duplicate names. So in this instance, I want to delete id 1, 2 and 4.

Thanks ahead of time...drop table table1
go
create table table1(id int
,iname varchar(10))
go
insert table1 select 1,'John'
insert table1 select 2,'John'
insert table1 select 3,'Mike'
insert table1 select 4,'John'
insert table1 select 5,'Dana'
insert table1 select 6,'Josh'
insert table1 select 7,'John'
go
select *
--delete
from table1
where iname in (select iname from table1 group by iname having count(*)>1)
and id not in (select max(id) from table1 group by iname having count(*)>1)|||Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates|||Originally posted by jiggle it
Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates
What do you mean "to re-insert"? Just run last query and all older reconds for duplicates will be gone. ;)|||I found the answer

http://aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=186|||DELETE Table1
WHERE id IN
(SELECT A.id from Table1 A, Table1 B
WHERE A.name = B.name
AND A.id < B.id)

May not be as efficient but requires less typing, which is a plus in my book )