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'.

though the question states SQL-SERVER, for whom search for MySql solution there is an easier faster way `UPDATE TX_Master_PCBA SET TIMESTAMP2=NOW() WHERE SERIAL_NO='050030309' ORDER BY TIMESTAMP DESC LIMIT 1` — Tomer W, Jul 23 '15 at 18:36
@TomerW - life saver, thank you. didn't even think to do a limit! saved me hours, ty — James Osguthorpe, Mar 23 '21 at 16:19

score 64 · Answer 1 · answered Nov 26 '14 at 09:36

64

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'

answered Nov 26 '14 at 09:36

Bobi

757
1
5
3

WORK FINE ! THANK YOU – sergioBertolazzo Oct 31 '19 at 18:20

score 31 · Answer 2 · edited May 23 '17 at 11:47

31

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

edited May 23 '17 at 11:47

Community

1
1

answered Sep 04 '15 at 10:00

panky sharma

2,029
28
45

What If I want to use Join here and set values from that table ? – Renascent Jan 24 '18 at 06:48

score 25 · Accepted Answer · answered Dec 13 '13 at 10:07

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
)

score 12 · Answer 4 · answered Dec 12 '13 at 09:19

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

fabriciorissetto · Answer 5 · 2018-04-26T02:41:31.767

12

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 
)

edited Apr 26 '18 at 02:41

answered Jun 13 '16 at 22:19

fabriciorissetto

9,475
5
65
73

2

+1 for thread safe, although when `AccountId` is not unique you're going to have to work in a cte as mentioned by https://stackoverflow.com/a/32395239/4496560 to grab only one single record. – Gregor y Apr 26 '18 at 02:36
I found that this SP updated ALL records so I made the top line look like this UPDATE Account Top (1) – Zenacity Jun 06 '19 at 19:32

score 7 · Answer 6 · answered Jun 29 '16 at 21:04

7

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

answered Jun 29 '16 at 21:04

Alexander Rivas

71
1
2

Update Top 1 record in table sql server

6 Answers6

Linked