locked
need Update of Select using TOP 1 RRS feed

  • Question

  • 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

     

    Thursday, September 21, 2006 8:47 PM

Answers

  • 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
    ---

    Friday, September 22, 2006 2:27 PM