Update with Order By

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);

SQL Server: UPDATE a table by using ORDER BY

No.

Not a documented 100% supported way. There is an approach sometimes used for calculating running totals called "quirky update" that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee.

But what version of SQL Server are you on? If SQL2005+ you might be able to do something with row_number and a CTE (You can update the CTE)

With cte As
(
SELECT id,Number,
ROW_NUMBER() OVER (ORDER BY id DESC) AS RN
FROM Test
)
UPDATE cte SET Number=RN

UPDATE with ORDER BY

UPDATE with ORDER BY

As to the question raised ion the title: There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order. But you have (limited) options to decide whether constraints are checked after each row, after each statement or at the end of the transaction. You can avoid duplicate key violations for intermediate states with a DEFERRABLE constraint.

I am quoting what we worked out under this question:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

NOT DEFERRED constraints are checked after each row.

DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE - which is the default - or via SET CONSTRAINTS) are checked after each statement.

There are limitations, though. Foreign key constraints require non-deferrable constraints on the target column(s).

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

Workaround

^{Updated after question update.}

Assuming "sequence" is never negative in normal operation, you can avoid unique errors like this:

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE  "CableLine" = 2;

UPDATE tbl SET "sequence" = "sequence" * -1
WHERE  "CableLine" = 2
AND    "sequence" < 0;

With a non-deferrable constraint (default), you have to run two separate commands to make this work. Run the commands in quick succession to avoid concurrency issues. The solution is obviously not fit for heavy concurrent load.

Aside:

It's OK to skip the key word AS for table aliases, but it's discouraged to do the same for column aliases.

I'd advice not to use SQL key words as identifiers, even though that's allowed.

Avoid the problem

On a bigger scale or for databases with heavy concurrent load, it's wiser to use a serial column for relative ordering of rows. You can generate numbers starting with 1 and no gaps with the window function row_number() in a view or query. Consider this related answer:

• Is it possible to use a PG sequence on a per record label?

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.

SQL Server order by in an update statement

You would need to select the rows to update in a derived table (subquery or commo table expression), then update:

with cte as (select top (100) stapel from kist where row = @row order by date)
update cte set stapel = @stapel

Update with Where, Order by and Limit does not work

You could use ROWID:

UPDATE MyTable 
SET Flag = 1 
WHERE ROWID IN (SELECT ROWID FROM MyTable WHERE ID = 5 
                ORDER BY OrderID DESC LIMIT 1);

db<>fiddle demo

or (ID,OrderID) tuple:

UPDATE  MyTable 
SET Flag = 1 
WHERE (ID, ORDERID) IN (SELECT  ID, ORDERID FROM MyTable WHERE ID = 5 
                        ORDER BY OrderID DESC LIMIT 1);

db<>fiddle demo2

---

And if you need to do it in bulk for every ID(SQLite 3.25.0):

WITH cte AS (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY OrderID DESC) AS rn FROM tab
)
UPDATE tab
SET Flag = 1
WHERE (ID, OrderID) IN (
       SELECT ID, OrderID
       FROM cte
       WHERE rn = 1
      );

SQL - UPDATE TABLE and ORDER BY?

Tables don't inherently have an order; you don't have to update them into any particular order.

What you do is choose the order of what you SELECT from the table. Then it can be in any order you choose!

Example:

SELECT * FROM movement  
ORDER BY timestamp; 

But then somewhere else maybe you want to:

SELECT * FROM movement  
ORDER BY timestamp DESCENDING; 

SQLite (Android) : UPDATE query with ORDER BY

As pointed out by many, this is definitely not the correct way to go.

But I found workaround in case someone else is looking for a similar implementation.

Here it is :

UPDATE myTable SET id = - (id + 1) WHERE id > 1;
UPDATE myTable SET id = - id WHERE id < 0;

This is a hack which I found here.

Again, this is not the correct way to go. But just posting a working solution I found.

---

Related Topics