Update Top 1 record in table sql server

Question

My Query   UPDATE TOP  1  TX Master PCBA   SET TIMESTAMP2    2013-12-12 15 40 31 593  WHERE SERIAL NO IN   0500030309    ORDER BY TIMESTAMP2 DESC    with serial No Column in TX Master PCBA table i have 10 records but i want to update the latest TIMESTAMP2 to current datetime   the above query is throwing error        Incorrect syntax near the keyword  TOP

User · Answer

Accepted answer of Kapil is flawed  it will update more than one record if there are 2 or more than one records available with same timestamps  not a true top 1 query        With cte as                       SELECT TOP 1  email fk FROM abc WHERE id  177 ORDER BY created DESC                      UPDATE cte SET email fk   10   Ref Remus Rusanu Ans -  SQL update top1 row query

User · Answer

When TOP is used with INSERT  UPDATE  MERGE  or DELETE  the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements  If you need to use TOP to insert  delete  or modify rows in a meaningful chronological order  you must use TOP together with an ORDER BY clause that is specified in a subselect statement    TOP cannot be used in an UPDATE and DELETE statements on partitioned views   TOP cannot be combined with OFFSET and FETCH in the same query expression  in the same query scope   For more information  see http   technet microsoft com en-us library ms189463 aspx

User · Answer

It also works well      Update t Set t TIMESTAMP2    2013-12-12 15 40 31 593  From       Select Top 1 TIMESTAMP2     From TX Master PCBA     Where SERIAL NO IN   0500030309       Order By TIMESTAMP2 DESC   t

User · Answer

WITH UpdateList view AS     SELECT TOP 1    from TX Master PCBA    WHERE SERIAL NO IN   0500030309      ORDER BY TIMESTAMP2 DESC     update UpdateList view  set TIMESTAMP2    2013-12-12 15 40 31 593

User · Answer

UPDATE TX Master PCBA SET TIMESTAMP2    2013-12-12 15 40 31 593   G FIELD  0000  WHERE TIMESTAMP2 IN       SELECT TOP 1 TIMESTAMP2    FROM TX Master PCBA WHERE SERIAL NO  0500030309     ORDER BY TIMESTAMP2 DESC   -- You need to decide what column you want to sort on

User · Answer

For those who are finding for a thread safe solution  take a look here   Code   UPDATE Account  SET    sg status    A  OUTPUT INSERTED AccountId --You only need this if you want to return some column of the updated item WHERE  AccountId          SELECT TOP 1 AccountId      FROM Account WITH  UPDLOCK  --this is what makes the query thread safe      ORDER  BY CreationDate

[sql-server] Update Top 1 record in table sql server

Examples related to sql-server

Examples related to sql-server-2008