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.

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.

delete from mytable 
where key <= 3000;

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.

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.

use the IN clause
or
simply write a procedure and pass the values. that u want to delete.

Member Avatar for Geek-Master

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.

Truncate table "tablename" it will delete all ur data from ur table

Truncate tables code worked a treat thanks :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.