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
How to Create Table Using Select Query in SQL Server
Find the Smallest Unused Number in SQL Server
SQL Query - Concatenating Results into One String
How to Calculate a Running Total in SQL Without Using a Cursor
How to Prevent a Database Trigger from Recursing
How to Add a Foreign Key to an Existing SQLite Table
MySQL - Selecting Data from Multiple Tables All with Same Structure But Different Data
Which SQL Query Is Better, Match Against or Like
Update One Table with Data from Another
Compare Datetime and Date Ignoring Time Portion
Concatenate Results from a SQL Query in Oracle
Compute Percents from Sum() in the Same Select SQL Query
Insert Command :: Error: Column "Value" Does Not Exist
How to Design a Database for User Defined Fields
How to Create a Copy of an Oracle Table Without Copying the Data
Select Data from Date Range Between Two Dates
Concatenate Two Database Columns into One Resultset Column
Combining the Results of Two SQL Queries as Separate Columns