Effective way to delete 2000 records out of 4000

Question

Post reply

Effective way to delete 2000 records out of 4000

Shin-435317

SSC-Addicted

Points: 402
More actions
May 10, 2007 at 8:50 am

#103359

Hi All
I have need to know of an effective way to delete 2000 records from a table holding 4000 records. Their is a unique key ie. originalno. which identifies each record and also I have the originalno. of all the records that need to be deleted.
But what syntax/way do I use to make this quick and correct rather than going through each originalno, and placing comers after each to extract the relevant records for deletion?
Many Thanks in advance.......

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

If you don't care which 2000 get deleted you can use the following which will delete the first 2000 rows it finds:

set rowcount 2000

delete from tablename

set rowcount 0

More than likely you do want only specific ones deleted and that gets more difficult unless you can post a copy of the table schema and a couple of sample record (4 records, 2 that you wouldn't want deleted and 2 that you would like deleted)

Baring that the following would work if the "originalno" values are sequential, or at least not interspersed with the values of the records to be kept:

delete from tablename where originalno < 2000

or

delete from tablename where originalno between 1000 and 3000

etc

Without knowing what the table looks like and the type of data it would be difficult to help further.

James.

John Rowan SSC Guru Points: 56440 More actions · Answer 2

You say that you have the OriginalNo values for the 2000 rows you want to delete. Where do you have these values? Please reply and include your table DDL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 3

I haven't tried this but I would assume you could use the folowing statement...

delete top 2000 from *table

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

John Rowan SSC Guru Points: 56440 More actions · Answer 4

The orginal post suggests that there are 2000 specific rows that need deleted.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Antares686 SSC Guru Points: 125444 More actions · Answer 5

You say you have the 2000 to delete? Where are they stored now.

Shawn McGehee Ten Centuries Points: 1239 More actions · Answer 6

If you are getting the 2000 IDs that you want deleted from some sort of query, you would use:

DELETE FROM Table_Name WHERE ID IN (*Query you used to pull the ID's you want deleted*)

If you have them some other way, it would depend on the data/organization of the list of IDs.

Shin-435317 SSC-Addicted Points: 402 More actions · Answer 7

Hi All

thanks for the posts, but I have 2000 records from 4000 in data type varchar and in a non sequential format which need to be deleted. Iam new to sql and need to delete them in a quick and effective way rather than me going through and manually inputting each record id then a comma (,) and then selecting delete records.

I look forward to your response.

Many Thanks

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 8

I doubt anyone is going to be able to help if you don't post the table definition, some sample data and the criteria you would use to do the deletes currently. Then we can see what you are trying to do and tell you the easiest way to accomplish it. If you provide about 4 sample records two of which meet your delete criteria someone should be able to quickly help you.

James.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 9

And please provide the query that allowed you to select which rows you want deleted! Stop making us guess!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)