34

The query below is working:

update  top(1) ShipBillInfo 
set     shipfirstname='kkk' 
where   CustomerId='134';

but it is showing error if i try to order by some Id: for example:

update  top(1) ShipBillInfo 
set     shipfirstname='kkk' 
where   CustomerId='134' 
order by 
        OredrGUID desc;
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
Tina
  • 341
  • 1
  • 3
  • 3
  • Some more information would be nice? Table info? Sample data? – Tim Oct 05 '10 at 05:09
  • 3
    Possible duplicate of [How to update and order by using ms sql](https://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql) – essential Apr 11 '18 at 07:45

4 Answers4

35
With cte as (
select  top(1) shipfirtsname  
From ShipBillInfo 
where   CustomerId='134' 
order by  OredrGUID desc)
Update cte set shipfirstname='abc';
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • @ErikE Did you mean multiplicity (1:many table relationships) or did you really mean concurrency (more than one user/connection changing the same row at the same time)? – yzorg Mar 14 '13 at 17:30
  • @yzorg I meant concurrency. Looking more carefully at what's actually being done, there probably isn't a big problem. It was more of a general note about this query pattern: where one wishes to update a row that is the "most recent" or "top/first". Without proper lock hints, depending on the usage scenario one can unexpectedly end up with two clients updating the same row, since the `TOP` part of the query can run concurrently from multiple clients (and thus they both acquire the same row) before the additional/escalated locks are acquired for the `UPDATE` part of the query. – ErikE Mar 14 '13 at 20:51
  • @yzorg But this only applies if the update itself would remove the row from consideration as the "most recent" one, or the update would be harmful to be repeated--not the case here. – ErikE Mar 14 '13 at 20:53
  • @ErikE: because the CTE is target of an UPDATE the plan must ensure stability of the rows seen. No need for hints. This is not a SELECT followed by UPDATE. – Remus Rusanu Mar 14 '13 at 21:05
  • 2
    @RemusRusanu I'm not sure that changes anything. Queries execute over time and have a lock acquisition life-cycle that can present concurrency problems when the rows selected are dependent on other rows' data (as is true with `ORDER BY`). This reading of other rows occurs BEFORE the update lock, so concurrency *is* an issue. If this particular query is executed by two clients at the same time there will be no harmful side-effects, so I probably was warning needlessly--I simply saw the potential for this query pattern's reuse causing problems, and noted it. – ErikE Mar 14 '13 at 22:26
  • 3
    @ErikE: *This reading of other rows occurs BEFORE the update lock*: no. Is the reading itself that take the U lock. That's what I mean by 'the CTE is target of an UPDATE'. The scan (or seek) will acquire U locks. – Remus Rusanu Mar 15 '13 at 07:22
  • 3
    @ErikE: *I probably was warning needlessly* if the case you believe to happen (scan with S lock or even nolock, then update) would happen then the warning would be very much warranted. The problem you mention *would* happen if the rows of interested would be acquired in a join or in a subquery, and lock hints would be necessary. But since the update is targeting directly the CTE, the engine knows what it has to do. – Remus Rusanu Mar 15 '13 at 07:26
  • I see what you mean. Thanks for weighing in. – ErikE Mar 15 '13 at 08:05
22

why dont you do :

update ShipBillInfo 
set shipfirstname='kkk' 
where OrderGUID = (select top (1) OrderGUID  
                   from ShipBillInfo 
                   where CustomerId = 134 
                   order by OredrGUID desc )
storm_buster
  • 7,362
  • 18
  • 53
  • 75
10

Thread Safe

For a thread safe solution none of the proposed solutions worked for me (some rows got updated more than once when executed it at the same time).

This worked:

UPDATE Account 
SET    sg_status = 'A'
WHERE  AccountId = 
(
    SELECT TOP 1 AccountId 
    FROM Account WITH (UPDLOCK) --this makes it thread safe
    ORDER  BY CreationDate 
)

If you want to return some column of the updated item you can put this in your update statement: OUTPUT INSERTED.AccountId (between the SET and WHERE)

fabriciorissetto
  • 9,475
  • 5
  • 65
  • 73
  • Did you experience deadlocks after adding the UPDLOCK? – Stephan Møller Sep 20 '18 at 14:13
  • In this case I'dont, Stephan. But I imagine that it can easily happen in cenaries where locks like this are used a lot. In my case I used this query to simulate a "queue" in the database, what is clearly an anti pattern... – fabriciorissetto Sep 20 '18 at 15:11
  • Well im designing the same thing :) – Stephan Møller Sep 21 '18 at 16:05
  • 2
    @StephanRyer since deadlocks are caused by 2 or more processes locking the same resources in a different order - to avoid deadlocks you can follow the advice from DDD grouping resources into aggregates and locking (for update) the same resource (a "root" resource) of the aggregate first in all transaction involving that group - in this way a lock on a row can actually improve the concurrency of a situation – Simon Dowdeswell Jul 06 '20 at 22:47
  • Yes good idea. You mean by using sp_getapplock for instance? – Stephan Møller Jul 08 '20 at 04:02
0

Update one record of a duplicate entry in VB.NET and SQL:

update top(1) display set actual = actual+1 where sku='" & TextBox1.Text & "' and actual <= plan_-1
  • display - table name
  • column name - sku , actual, plan_
  • TextBox1.Text - Component of VB to get input
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Gurjar
  • 1