Update Table and Set for top rows from select statement

Question

Got a single SQL table which I am wanting to query and update top 5 rows when doing so. The following does not work (hence the question) but might explain what it is I am trying:

UPDATE 
    TempStaging
SET 
    pending = 'running'
FROM 
    (SELECT TOP 5 scan FROM TempStaging
         WHERE pending = 'outstanding')

UPDATE has TOP clause, also exists duplicate article [how can I Update top 100 records in sql server](http://stackoverflow.com/questions/1198364/how-can-i-update-top-100-records-in-sql-server) — Alexey, Apr 22 '16 at 14:02
Assuming that you have some sort of key on which you are ordering the records: — C. White, Apr 22 '16 at 14:02
Your query is wrong. After `FROM` you should use a table name but you used a SELECT-statement. — Siyavash Hamdi, Apr 22 '16 at 14:06

Giorgos Betsos · Accepted Answer · 2016-04-22T14:19:18.130

4

You can use a CTE to UPDATE:

;WITH ToUpdate AS (
   SELECT TOP 5 pending  
   FROM TempStaging
   WHERE pending = 'outstanding'
)
UPDATE 
    ToUpdate
SET 
    pending = 'running'

Note: TOP used without an ORDER BY clause will select 5 arbitrarily specified records.

Edit: To get the Name values of the records being updated you can use the OUTPUT clause, as already noted in the comment by @C. White:

;WITH ToUpdate AS (
   SELECT TOP 5 pending, name  
   FROM #TempStaging
   WHERE pending = 'outstanding'
)
UPDATE ToUpdate
SET pending = 'running'
OUTPUT inserted.name

edited Apr 22 '16 at 14:19

answered Apr 22 '16 at 13:57

Giorgos Betsos

71,379
9
63
98

Thanks - If have 2 columns name & pending how do I output name for these same top 5 rows? – dross Apr 22 '16 at 14:05
@dross What do you mean by *output name*? This is an `UPDATE` statement. – Giorgos Betsos Apr 22 '16 at 14:07
1

For this example: ;WITH ToUpdate AS ( SELECT TOP 5 pending FROM TempStaging WHERE pending = 'outstanding' ) UPDATE ToUpdate SET pending = 'running' OUTPUT inserted.pending ,inserted.name – C. White Apr 22 '16 at 14:09
@Giorgos Betsos: I would like the name value for the affected rows to be returned by the statement. – dross Apr 22 '16 at 14:11

score 1 · Answer 2 · answered Apr 22 '16 at 14:05

The most straightforward way is to use the UPDATE's WHERE clause to select the values... embedding an IN (SELECT TOP 5 ...) should do it.

Example:

create table ##dummy (pk int IDENTITY (1,1) PRIMARY KEY, vals varchar(50));
INSERT INTO ##dummy (vals) VALUES 
  ('updateme')
  ,('updateme')
  ,('updateme')
  ,('updateme')
  ,('updateme')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')
  ,('leavemealone')

SELECT * FROM ##dummy
UPDATE ##dummy
  SET vals = 'fixed!'
WHERE 
  pk in (SELECT TOP 5 pk FROM ##dummy ORDER BY pk)
SELECT * FROM ##dummy

DROP TABLE ##dummy

So with your particular example, it is probably:

UPDATE 
  TempStaging
SET 
  pending = 'running'
WHERE
  scan IN (SELECT TOP 5 scan FROM TempStaging WHERE pending = 'outstanding' ORDER BY scan)

Hope that helps!

With original question I asked in mind I have marked Giorgos Betsos answer as correct for the sake of clarity. — dross, Apr 22 '16 at 14:18

score 0 · Answer 3 · answered Apr 22 '16 at 13:59

0

Try this

UPDATE Top(5) TempStaging SET pending = 'running' WHERE pending = 'outstanding'

answered Apr 22 '16 at 13:59

Akshey Bhat

8,227
1
20
20

Update Table and Set for top rows from select statement

3 Answers3