SQL UPDATE TOP with ORDER BY?

Question

I have a following query:

UPDATE TOP (@MaxRecords) Messages 
SET    status = 'P' 
OUTPUT inserted.* 
FROM   Messages 
where Status = 'N'
and InsertDate >= GETDATE()

In the Messages table there is priority column and I want to select high priority messages first. So I need an ORDER BY. But I do not need to have sorted output but sorted data before update runs.

As far as I know it's not possible to add ORDER BY to UPDATE statement. Any other ideas?

Possible duplicate of [SQL update top1 row query](http://stackoverflow.com/questions/3860975/sql-update-top1-row-query) — fabriciorissetto, Jun 13 '16 at 22:17
Possible duplicate of [How to update and order by using ms sql](http://stackoverflow.com/questions/655010/how-to-update-and-order-by-using-ms-sql) — Athafoud, May 22 '17 at 07:39
Does this answer your question? [how can I Update top 100 records in sql server](https://stackoverflow.com/questions/1198364/how-can-i-update-top-100-records-in-sql-server) — Michael Freidgeim, Jun 08 '22 at 11:06

score 54 · Accepted Answer · answered Oct 25 '13 at 08:05

54

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.

answered Oct 25 '13 at 08:05

Roman Pekar

107,110
28
195
197

Is `output inserted` valid here? This is an UPDATE. Is this a copy/paste error? – Simon_Weaver Jan 05 '21 at 06:45
2

@Simon_Weaver, yes its correct. INSERTED Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. Source : https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15 – Loki Jun 16 '21 at 15:35

score 19 · Answer 2 · answered Oct 25 '13 at 08:04

19

You can try sub query like

  UPDATE Messages 
    SET    status = 'P' 
    WHERE MessageId IN (SELECT TOP (@MaxRecords) MessageId FROM Messages where Status = 'N' and InsertDate >= GETDATE() ORDER BY Priority)
output inserted.*

answered Oct 25 '13 at 08:04

Upendra Chaudhari

6,473
5
25
42

In my case I wasn't using TOP because I wanted to update all matched records by the WHERE clause and I got the error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified." Adding a dummy TOP(100000) make it work. I don't know if there is a better solution in my case. – Augusto Barreto Nov 10 '16 at 19:54
2

@AugustoBarreto you can use `top 100 percent` to take all the records instead of a dummy number. – Athafoud May 22 '17 at 07:40
Thanks! I didn't know that. Reading the docs: "Limits the rows returned in a query result set to a specified number of rows or percentage of rows" – Augusto Barreto May 22 '17 at 12:04

score -11 · Answer 3 · answered Oct 25 '13 at 08:03

-11

the correct syntax of update is

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

answered Oct 25 '13 at 08:03

R R

2,999
2
24
42

1

This is MySql syntax, not very useful. – Kabwla-TwoLips Apr 08 '19 at 12:53

SQL UPDATE TOP with ORDER BY?

3 Answers3

Linked

Related