SQL Update Top with Order By

SQL UPDATE TOP with ORDER BY?

you can use common table expression for this:

;with cte as (
   select top (@MaxRecords)
       status
   from Messages 
   where Status = 'N' and InsertDate >= getdate()
   order by ...
)
update cte set
    status = 'P'
output inserted.*

This one uses the fact that in SQL Server it's possible to update cte, like updatable view.

How to UPDATE TOP(n) with ORDER BY giving a predictable result?

SQL Server allows you to update a derived table, CTE or view:

UPDATE x
SET 
    IsDone = 1
OUTPUT
    inserted.Id,
    inserted.Etc
FROM (
select TOP (N) *
FROM
    QueueTable 
WHERE
    IsDone = 0
ORDER BY
    CreatedDate ASC;
) x

No need to compute a set of IDs first. This is faster and usually has more desirable locking behavior.

how can I Update top 100 records in sql server

Note, the parentheses are required for UPDATE statements:

update top (100) table1 set field1 = 1

SQL UPDATE TOP () or UPDATE with SELECT TOP

First statement will be faster. But the top 150 records are chosen randomly. Records updated in both the queries might not be same. Since you are spitting the updates into batches your approach may not update all records.

I will do this using following consistent approach than your approach.

;WITH cte
     AS (SELECT TOP (350) value1,
                          value2,
                          value3
         FROM   database1
         WHERE  value1 = '123'
         ORDER  BY ID -- or any other column to order the result 
        )
UPDATE cte
SET    value1 = '',
       value2 = '',
       value3 = '' 

Also you don't have to worry transaction log size when updating couple thousands records there is no need of batches here

Update Top 1 record in table 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
)

SQL SERVER - UPDATE TOP with ORDER BY and hints: ROWLOCK, READPAST,XLOCK

;With CTE as
(
    select Top 1 *  from [queue]  WITH (XLOCK, READPAST, ROWLOCK) order by YourColumnName
     --you can your own cond...and you can select Top number of rows..here i'm selecting only 1 row
)

Update CTE set status = 2

How to update top 1 row in SQL Server

Try this:

UPDATE CheckInCheckOut
SET CountHours = @Result
WHERE UserId_Fk = (SELECT TOP 1 UserId_Fk 
                   FROM CheckInCheckOut 
                   ORDER BY [Your_Sort_Column])

Update based on group by, top 1 row and where case

Just slightly modify your current query by assigning a row number, across each ParentID group. The ordering logic for the row number assignment is that records with IsOnTop values of 1 come first, and after that the OrderBy column determines position. I update the CTE under the condition that only the first record in each ParentID group gets assigned a Default value of 1.

WITH cte AS (
    SELECT ParentID, ID, [Default], IsOnTop, OrderBy,
        ROW_NUMBER() OVER (PARTITION BY ParentID
                           ORDER BY IsOnTop DESC, OrderBy) rn
    FROM [table]
    WHERE ParentID IN (SELECT ParentID FROM [table]
                       GROUP BY ParentID HAVING SUM([Default]) <> 1)
)

UPDATE cte
SET [Default] = 1
WHERE rn = 1;

How to update and order by using ms sql

You can do a subquery where you first get the IDs of the top 10 ordered by priority and then update the ones that are on that sub query:

UPDATE  messages 
SET status=10 
WHERE ID in (SELECT TOP (10) Id 
             FROM Table 
             WHERE status=0 
             ORDER BY priority DESC);

---

Related Topics