944,155 Members | Top Members by Rank

Web Development > Databases > MS SQL > how to delete data from table
Ad:
  • MS SQL Discussion Thread
  • Marked Solved
  • Views: 27752
  • MS SQL RSS
Permalink
Jun 21st, 2007
0

how to delete data from table

Expand Post »
hello,

i have a table having 4000 (four thousand) records and now i want to delete 3000 of them as i don't need them, these records have primary key as id which is ofcourse unique.
can anyone tell me how can i delete these records with one query.
if their is any other method please let me know.

Thanks in advance.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Es Sayen is offline Offline
19 posts
since Jun 2007
Permalink
Jun 23rd, 2007
0

Re: how to delete data from table

So is there any special criteria which distiguishes these 3000 records from the other records. If there is, you can use that clause to delete the required rows. If this is just a mass delete and if your primary key is ordered, you can delete the rows based on the value of primary keys.

sql Syntax (Toggle Plain Text)
  1. DELETE FROM mytable 
  2. WHERE key <= 3000;
Super Moderator
Featured Poster
Reputation Points: 3241
Solved Threads: 719
Failure as a human
~s.o.s~ is offline Offline
8,873 posts
since Jun 2006
Permalink
Jun 25th, 2007
0

Re: how to delete data from table

hello,

no there is no pattern and i have primary key to distinguish those records and i have to delete them randomly.
e.g. from 1-4000 series, i have to delete 2-7, 9-11, 25-36, 40, 45, 78 and so on, without any pattern.

Thank You.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Es Sayen is offline Offline
19 posts
since Jun 2007
Permalink
Jun 25th, 2007
0

Re: how to delete data from table

If you don't care about the values which get deleted, then why not just use the method suggested by me above? If not, then you need to know Transact SQL to generate random numbers and delete entries with those primary keys.
Super Moderator
Featured Poster
Reputation Points: 3241
Solved Threads: 719
Failure as a human
~s.o.s~ is offline Offline
8,873 posts
since Jun 2006
Permalink
Jun 28th, 2007
0

Re: how to delete data from table

use the IN clause
or
simply write a procedure and pass the values. that u want to delete.
Featured Poster
Reputation Points: 665
Solved Threads: 427
Posting Genius
debasisdas is offline Offline
6,406 posts
since Feb 2007
Permalink
Jul 6th, 2007
0

Re: how to delete data from table

As a long term fix, I would suggest altering the data table to include a code or date field to go by in the future. That's if you have authorization to alter the data table.
Reputation Points: 12
Solved Threads: 6
Junior Poster
Geek-Master is offline Offline
156 posts
since Dec 2004
Permalink
Nov 4th, 2008
0

Re: how to delete data from table

Truncate table "tablename" it will delete all ur data from ur table
Reputation Points: 10
Solved Threads: 0
Newbie Poster
endrum18 is offline Offline
1 posts
since Sep 2008
Permalink
Sep 29th, 2009
0

Re: how to delete data from table

Truncate tables code worked a treat thanks
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ultimahosts is offline Offline
2 posts
since Sep 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MS SQL Forum Timeline: Anyway to use Having in this SQL STMT?
Next Thread in MS SQL Forum Timeline: How to connect two tables





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC