May 10, 2007 at 8:50 am
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.......
May 10, 2007 at 9:56 am
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.
May 10, 2007 at 10:29 am
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.
May 10, 2007 at 10:42 am
I haven't tried this but I would assume you could use the folowing statement...
delete top 2000 from *table
Ben Sullins
bensullins.com
Beer is my primary key...
May 10, 2007 at 10:51 am
May 10, 2007 at 11:33 am
You say you have the 2000 to delete? Where are they stored now.
May 10, 2007 at 12:56 pm
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.
May 11, 2007 at 3:35 am
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
May 11, 2007 at 6:07 am
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.
May 11, 2007 at 6:32 am
And please provide the query that allowed you to select which rows you want deleted! Stop making us guess!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply