How to Update and Order by Using Ms SQL

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.

Update SQL Server query with order by clause

It seems like you want to sort the rows in the descending order of stepId, then assign new consecutive values to stepId but this time in the ascending order, so essentially you want to reverse the order of rows if stepId was the sorting criterion. Or, using an example (which you haven't provided), turn a row set like this:

stepId  someColumn
------ ----------
7 AAA
4 BBB
2 DDD
1 CCC

into this:

stepId  someColumn
------ ----------
1 AAA
2 BBB
3 DDD
4 CCC

If that is indeed so and if you are using SQL Server 2005 or later version, you could use a CTE and the ROW_NUMBER() function like this:

WITH newIDs AS (
SELECT
stepId,
newStepId = ROW_NUMBER() OVER (ORDER BY stepId DESC)
FROM atable
)
UPDATE newIDs
SET stepId = newStepId
;

The ROW_NUMBER function assigns row numbers in the descending order of stepId, returning them alongside corresponding current values of stepId. Since the newIDs CTE derives rows from a single table, that makes the CTE updatable, so you can use it as the target of UPDATE and simply assign every stepId the newly generated row number.

How to Update a table while sorting based on Date column in SQL?

Use a CTE:

Check it here: http://rextester.com/NGTM78965

create table #t1 (id int, d datetime, rown int);
insert into #t1 values (1,'2016-05-01',0);
insert into #t1 values (2,'2016-05-04',0);
insert into #t1 values (3,'2016-06-01',0);
insert into #t1 values (4,'2016-04-03',0);
insert into #t1 values (5,'2016-05-12',0);
insert into #t1 values (6,'2016-08-01',0);
insert into #t1 values (7,'2016-05-15',0);
insert into #t1 values (8,'2016-05-25',0);

with cte (id, d, r) as
(
select id, d, row_number() over (order by d) r
from #t1
)
update #t1
set #t1.rown = cte.r
from cte inner join #t1 on #t1.id = cte.id;

select * from #t1 order by d

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;

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 to update SQL Server Table after query sort by datetime


I want to keep or the data sort by datetime.

You just can't. SQL tables represent unordered sets of rows, so you cannot actually order the stored data. There is no inherent or default data ordering that you could modify.

You might notice that rows appear to be returned in the same order when you run the same query again, but database engines do not whatsoever guarantee that, and the results you see today may change in the future.

Whenever you need to retrieve the data in a given order, you do need to use an order by clause. Otherwise, the ordering is undefined, meaning that the database is free to return the rows in any order it likes.

How do I UPDATE from a SELECT in SQL Server?


UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE
Table_A.col3 = 'cool'


Related Topics



Leave a reply



Submit