CODEWITHSUNDEEP
sql-server
largo68
largo68

Reputation: 629

SQL Server: how to update a column for a rows range?

Working environment is SQL Server 2000. I have a table with no indexes, no PK... Total number of rows is 600,000.

How can I update a column from row 0 -> 100,000 with a value then from 100,001 -> 200,000 with another, and so on?

Thank you.

Upvotes: 1

Views: 5294

Answers (5)

Deepak Nirala
Deepak Nirala

Reputation: 836

UPDATE table_name SET column=value 
WHERE id IN(SELECT id FROM table_name WHERE id BETWEEN 10 AND 20);

Upvotes: 1

Jason Goemaat
Jason Goemaat

Reputation: 29234

set all values to null, then SET ROWCOUNT 100000. Then do successive updates ( each will affect 100000 rows) with different values where that column IS NULL. @@rowcount will be the number of rows affected after each update, so stop when it is less than 100000.

For @Shannon's comment, the ROWCOUNT will not be honored for update/delete/insert statements in the next version of SQL Server (post-SQL Server 2008), but it will work fine for SQL Server 2000. The recommended change is to use the TOP clause, but I don't think that is supported for updates until SQL Server 2005.

I think you could sue a cursor if you wanted...

update mytable set myid = null

SET NOCOUNT ON -- prevent all those "1 row(s) updated" messages
declare @count int, @value int, @myid int
set @count = 1
set @value = 1
declare cursor_update cursor for select myid from mytable
open cursor_update
fetch cursor_update into @myid
while @@FETCH_STATUS = 0
begin
    update mytable set myid = @value where current of cursor_update

    set @count = @count + 1
    if (@count > 100000) 
    begin
        set @count = 1
        set @value = @value + 1
    end

    fetch cursor_update into @myid
end
close cursor_update
deallocate cursor_update

Upvotes: 3

Shannon Severance
Shannon Severance

Reputation: 18410

Note: I believe this works on SQL Server 2000, but do not have that version to test against.

To change an indeterminate set of rows use a top query like:

drop table t

create table t (c varchar(20))

insert into t
select top 15 'unchanged' from information_schema.columns

update alias
set c = 'changed'
from (select top 5 * from t) alias

-- note later queries need to be able to look at data
-- to tell if the row has already been processed.
update alias
set c = 'another change'
from (select top 5 * from t where c = 'unchanged') alias

select * from t

Upvotes: 2

Andriy M
Andriy M

Reputation: 77737

In SQL Server 2005+ you could use ranking to assign relative row numbers based on an arbitrary or specific order:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
  FROM table
)
UPDATE ranked
SET column = CASE
  WHEN rn BETWEEN      1 AND 100000 THEN value1
  WHEN rn BETWEEN 100001 AND 200000 THEN value2
  …
END

Replace (SELECT 1) with a column list to use a specific order.

Upvotes: 1

Martin
Martin

Reputation: 1458

You were probably thinking of the old times, in IBM's SQL-DS in a VSAM datafile, where you were able to get the RelativeRecordNumber in the physical file?

Unfortunately (or should I say fortunately) that's history.

Upvotes: 0

Related Questions