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 'Al开发者_如何学Goice'.

How would you write the appropriate query?

---

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

---

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

---

In SQL Server:

DELETE TOP 1 FROM EMPLOYEE WHERE Name = 'Alice'

---

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

---

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
*/