Count-Replace in SQL 1

[Mbroad]

I am a SQL newbie, so excuse the obvious question--I have a table which has a two-digit numeric value in it and approx. 21000 rows in the table. I need to modify (update) this number in the first 1900 rows to "01" and to "16" in the remaining rows. How do I accomplish this?
Thanks,

Government Coder Mark

 

[jbenson001]

Is there an Identity column in the table, or a numeric column that is unique that shows the sequence of rows?

 

[Mbroad]

Not sure what that means--sorry (newbie)

Government Coder Mark

 

[Mbroad]

I don't have a key number or identity number--I would have to have the SQL count the rows....

Government Coder Mark

 

[r937]

count the rows in which sequence?

how do you know which ones are the "first" 1900 rows?

rows are not stored in any particular sequence

r937.com | rudy.ca

 

[Mbroad]

it does not matter what 1900 rows I select, the 1st 1900 are adequate---Thanks

Government Coder Mark

 

[SQLDenis]

then do something like this

set rowcount 1900
update table set column = '01'

set rowcount 0
GO

update table set column = '16'
where column <> '01'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mbroad]

Addendum to this question-The code (above) will not work as ALL of the records in tyhe tabel have "01" in the column value to start. I need to keep 1900 of these as "01" and update the remaining rows to "16". How do I accomplish this?

Thanks very much!


Government Coder Mark

 

[SQLDenis]

set rowcount 19100 --(21000 -1900)
update table set column = '16'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Mbroad]

Thanks--Will this leave the first 1900 rows intact with the "01" still there? Thanks

Government Coder Mark

 

[SQLDenis]

yes, it will only update 19100 rows

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Here is a test script

create table Test (id int)
insert Test values (1)
insert Test values (1)
insert Test values (1)
insert Test values (1)

select * from Test --all1

set rowcount 2
update Test 
set id = 666

set rowcount 0

select * from Test --2 are 1 , 2 are 666

drop table Test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/