How to update the Top (20) records with using Select Query

Question

How to update the Top (20) records with using Select query?

Table1 (table records are in for loop) has 60 records. At a time, I want to get first 20 records based on table column="TEXT", then update those 20 records with column="TEXT1".

After that, I will pick the next 20 records (21-40) and again update as above.

I'm using below query, but it will update the first 20 records after next 20 (21-40) records it will not work.

Why 20 at a time? Are you dealing with millions of records here? Generally databases work best on set based operations. and updating 60 records to say "TEXT1" in a single update statement would be far more efficient. — xQbert, Jul 10 '18 at 17:32
What version of SQL Server? some support fetch some others we'd have to generate a row number and useit.. — xQbert, Jul 10 '18 at 17:37
You can use a CTE to run the subquery / get the top 20 results, then run the update against that... but that can be prone to locking... — JohnLBevan, Jul 10 '18 at 19:01
https://stackoverflow.com/questions/19584315/sql-update-top-with-order-by — JohnLBevan, Jul 10 '18 at 19:02
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Jul 10 '18 at 19:57

score 2 · Answer 1 · edited Jul 10 '18 at 17:38

2

Update tableName set Column = 'TEXT1' where column = 'TEXT'  ;

or if need be conditionally update...

UPDATE tablename set column = case when column = 'TEXT' then 'Text1'
                                   else column = 'OtherText' then 'Text1Other'
                                   else column = 'StillOtherText' then 'Text1Other2' end
WHERE column in ('TEXT','OtherText','StillOtherText');

edited Jul 10 '18 at 17:38

Evaldas Buinauskas

13,739
11
55
107

answered Jul 10 '18 at 17:36

xQbert

34,733
2
41
62

this is the good solution: rather than the loop, the single query cover every expect with case – Ajay2707 Jul 10 '18 at 18:58

score 1 · Answer 2 · answered Jul 10 '18 at 18:18

One way is to use a while-loop that checks for the existence of 'TEXT'. If the check returns true, then the top 20 primary keys are selected as part of an update statement.

WHILE EXISTS (SELECT * 
                FROM Table1 
               WHERE yourcolumn = 'TEXT')
BEGIN
UPDATE Table1
   SET yourcolumn = 'TEXT1'
 WHERE primarykey IN (
                       SELECT TOP 20 primarykey
                         FROM Table1
                        WHERE yourcolumn = 'TEXT'
                     )
END

i think this could be enhanced by checking for the row count of the updated query rather than doing a while exists which does a full table scan — Ctznkane525, Jul 11 '18 at 00:44

How to update the Top (20) records with using Select Query

2 Answers2