CODEWITHSUNDEEP
sqlsql-server
Si8
Si8

Reputation: 9225

How to delete all rows in sql except one

I have the following query:

DELETE FROM [DSPCONTENT01].[dbo].[Contact Center]
WHERE [Contact Center] IS NULL
  AND [F2] IS NULL

How can I modify the query so after execution ONLY row 15 is still showing and every other row is deleted?

Upvotes: 3

Views: 12261

Answers (3)

arin1405
arin1405

Reputation: 677

Some other way to delete except the specific row:

DELETE FROM [DSPCONTENT01].[dbo].[Contact Center] WHERE F10 NOT IN (2096)

You can also Select the particular record into a temporary table, then drop the original table and finally copy the data from the temporary table to the original table. Something like this:

create table #ContactCenter as
select * from [DSPCONTENT01].[dbo].[Contact Center] where F10 = 2096

truncate table [DSPCONTENT01].[dbo].[Contact Center]

insert into [DSPCONTENT01].[dbo].[Contact Center]
    select * from #ContactCenter

Upvotes: 5

Crono
Crono

Reputation: 10478

As of now you could simply do this, assuming this record is the only one having a F10 value of 2096:

DELETE FROM [DSPCONTENT01].[dbo].[Contact Center] 
WHERE F10 <> 2096

However, in the long run you are bound to have other problems with such a design. This table needs a primary key and proper field definitions. Urgently.

Upvotes: 2

Deepak Sharma
Deepak Sharma

Reputation: 4170

you mean you want to delete all the row except 115?

if so then use the below one --

DELETE FROM [DSPCONTENT01].[dbo].[Contact Center] 
WHERE ([Contact Center] IS NOT NULL OR [F2] IS NULL)

Upvotes: 1

Related Questions