更新表sql server中的前1条记录[重复]

This question already has an answer here:

这个问题在这里已有答案：

How to update and order by using ms sql 5 answers
如何使用ms sql 5答案更新和订购

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.

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

the above query is throwing error :

上面的查询抛出错误：

Incorrect syntax near the keyword 'TOP'.

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

6 个解决方案

#1

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'

#2

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
)

#3

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.

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

Ref Remus Rusanu Ans:- SQL update top1 row query

Ref Remus Rusanu Ans： - SQL更新top1行查询

#4

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与INSERT，UPDATE，MERGE或DELETE一起使用时，引用的行不按任何顺序排列，并且不能在这些语句中直接指定ORDER BY子句。如果需要使用TOP以有意义的时间顺序插入，删除或修改行，则必须将TOP与subselect语句中指定的ORDER BY子句一起使用。

TOP cannot be used in an UPDATE and DELETE statements on partitioned views.

TOP不能在分区视图的UPDATE和DELETE语句中使用。

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

TOP不能与OFFSET和FETCH在同一查询表达式中组合（在同一查询范围内）。有关详细信息，请参阅http://technet.microsoft.com/en-us/library/ms189463.aspx

#5

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

#6

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 
)

#1

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'

#2

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
)

#3