更新表sql server中的前1条记录

Question

我的疑问

UPDATE TOP (1) TX_Master_PCBA  
SET TIMESTAMP2 = '2013-12-12 15:40:31.593'
WHERE SERIAL_NO IN ('0500030309') 
ORDER BY TIMESTAMP2 DESC 

使用serial_No列在TX_Master_PCBA表中，我有10条记录，但我希望将最新的TIMESTAMP2更新为当前的日期时间。

上面的查询正在抛出错误：

关键字“TOP”附近的语法不正确。

Answer 1

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
)

Answer 2

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'

Answer 3

Kapil的公认答案是有缺陷的，如果有2条或多条有相同时间戳可用的记录，它将更新多条记录，而不是真正的前1位查询。

    ;With cte as (
                    SELECT TOP(1) email_fk FROM abc WHERE id= 177 ORDER BY created DESC   
            )
    UPDATE cte SET email_fk = 10

参考文献Rusanu Ans:- SQL update top1 row query