Saturday, February 25, 2012

need Update of Select using TOP 1

I need to update a table as follows:

Update item1, item2, item3, item4 in a table where column1="email_address" and column2 is ordered acending so the most recent entry for a specific user is updated.

column2 is a time stamp.

Pseudo code something like this:

UPDATE Table1 SET item1, item2, item3, item4 WHERE Table1 is TOP 1 ORDERED BY column2 ASC column1="email_address"

So i want to update only the most recent entry of a customer with a specific email address. I can't get the SQL command together that will do that.

I aint that bright so specific syntax always helps.

Thanks,

Bill

Are you using SQL Server 2005 ? Then you should have a look on:

Limiting Updated Data by Using TOP

If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. The following example updates the vacation hours of the 10 employees with the earliest hire dates.


UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

No comments:

Post a Comment