SQL Server order by in an update statement

Question

I have this query and I can't seem to figure out how to use it in combination with order by. This is the query:

UPDATE TOP 100 Kist SET Stapel ='" + Stapel + "' WHERE Row ='" + Row + "'"

Each row in 'kist' has a date and I want to order my update statement by using those dates. Like so:

UPDATE TOP 100 Kist SET Stapel ='" + Stapel + "' WHERE Row ='" + Row + "'"
ORDER BY Date ASC

But this does not work.

Any help would be much appreciated.

possible duplicate https://stackoverflow.com/questions/2334712 — devio, May 17 '20 at 17:32
Something seem terribly wrong here. There is a syntax error in the where clause. Is that what you mean by "doesn't work"? Is this dynamic sql of some sort? — SMor, May 17 '20 at 18:01
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, May 17 '20 at 20:35
@Smor, it seems probably wrong because it is a query with C# variables in it.. — Lucdabomb, May 18 '20 at 08:10

score 1 · Accepted Answer · answered May 17 '20 at 17:26

1

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

answered May 17 '20 at 17:26

GMB

1

either CTE, or `UPDATE FROM query`, or `UPDATE FROM SELECT` https://stackoverflow.com/questions/2334712 – devio May 17 '20 at 17:32

1 Answers1