CODEWITHSUNDEEP
sqlsql-server
Ahmet Altun
Ahmet Altun

Reputation: 4039

Delete only one record on SQL Server

Assume that there is a table EMPLOYEE with columns ID (int), Name (nvarchar), Surname(nvarchar) on SQL Server.

This is ALL that you know. You cannot see the data inside the table.

You are ordered to delete ONLY ONE of the records whose name is 'Alice'.

How would you write the appropriate query?

Upvotes: 1

Views: 1674

Answers (5)

granadaCoder
granadaCoder

Reputation: 27852

declare @Holder table ( EmployeeKey int , Name varchar(24) , Surname varchar(24) )


Insert into @Holder ( EmployeeKey , Name , Surname )
            select 201 , 'Alice' , 'Smith'
union all   select 102 , 'Mary' , 'Smith'
union all   select 203 , 'Alice' , 'Henderson'
union all   select 104 , 'John' , 'Smith'
union all   select 105 , 'Paul' , 'Johnson'
union all   select 206 , 'Alice' , 'Jones'

Delete @Holder
/*  Select * ............. while debugging, you can remove the 'Delete @Holder' and put in a 'Select *' to see the results (of what ~will be deleted) */
From 
@Holder h
join 
(select top 1 EmployeeKey from @Holder innerH where Name = 'Alice' /* order by EmployeeKey DESC */ ) as derived1
 on h.EmployeeKey = derived1.EmployeeKey

select * from @Holder order by Name , EmployeeKey

/*
order by EmployeeKey DESC is optional, aka, if you want to "prefer" which one gets popped for delete, you can tweak it as needed
*/

Upvotes: 0

Mazen Elkashef
Mazen Elkashef

Reputation: 3499

All the suggested answers here are mainly similar and all of them is optimum as far as you introduced your problem.

but this will force your code to delete the first record with Name = 'Alice'. But if you need to have a little extra power to choose which 'Alice' to delete if there's more than one in your table. but of course the ID has to be a Primary Key or Unique

SELECT FROM EMPLOYEE ID, Surname WHERE Name = 'Alice'

This will show you the results then you can take the ID of the target record to be deleted and place it in the following query (let's say the ID of the record you want to delete is 56)

DELETE FROM EMPLOYEE  WHERE ID = 56

Upvotes: 1

Stefan
Stefan

Reputation: 4206

In SQL Server: 

DELETE TOP 1 FROM EMPLOYEE WHERE Name = 'Alice'

Upvotes: 3

trickwallett
trickwallett

Reputation: 2468

DELETE TOP (1) FROM EMPLOYEE WHERE Name = 'Alice'

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453212

DELETE TOP (1)
FROM EMPLOYEE 
WHERE Name='Alice'

Upvotes: 10

Related Questions