Update Top 1 record in table sql server
Sql ServerSql Server-2008Sql Server Problem Overview
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'.
Sql Server Solutions
Solution 1 - Sql Server
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'
Solution 2 - Sql Server
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:- https://stackoverflow.com/questions/3860975/sql-update-top1-row-query#
Solution 3 - Sql Server
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
)
Solution 4 - Sql Server
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
Solution 5 - Sql Server
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
)
Solution 6 - Sql Server
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