Update only one row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update only one row

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-21 : 09:05:49

i need to update only one row fro m a group of same records.
create table test
(
na varchar(10),
age int,
ctr char(10),
)

insert into test
select 'y',1,null
union all
select 'y',1,null
union all
select 't',2,null
union all
select 't',2,null
union all
select 't',2,null

output should come as
na age ctr
-------------
y 1 one
y 1 null
t 2 two
t 2 null
t 2 null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 09:19:18

Using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-21 : 10:28:27

Using sql2000 Mr.peso

quote:
Originally posted by Peso

Using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2008-05-21 : 11:03:47

You need something distinct to reference to.
Like an ID field or entrydate field or a unique constraint across mutiple fields.

Jim
Users <> Logic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 11:18:19

[code]declare @test table
(
na varchar(10),
age int,
ctr char(10)
)

insert into @test
select 'y',1,null
union all
select 'y',1,null
union all
select 't',2,null
union all
select 't',2,null
union all
select 't',2,null

select * from @test

declare @stage table (rowid int identity(1, 1), na varchar(10), age int)

insert @stage select distinct na, age from @test

select * from @stage

declare @rowid int

select @rowid = max(rowid)
from @Stage

set rowcount 1

while @rowid > 0
begin
update t
set t.ctr = '1'
from @test as t
inner join @stage as s on s.na = t.na and s.age = t.age
where s.rowid = @rowid

set @rowid = @rowid - 1
end

set rowcount 0

select * from @test[/code]

E 12°55'05.25"
N 56°04'39.16"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-22 : 00:38:00

Txs Mr .Peso its working fine.Again Txs a lot

quote:

Originally posted by Peso

declare @test table
(
na varchar(10),
age int,
ctr char(10)
)

insert into @test 
select 'y',1,null
union all 
select 'y',1,null
union all 
select 't',2,null
union all 
select 't',2,null
union all 
select 't',2,null

select * from @test

declare @stage table (rowid int identity(1, 1), na varchar(10), age int)

insert @stage select distinct na, age from @test

select * from @stage

declare @rowid int

select @rowid = max(rowid)
from	@Stage

set rowcount 1

while @rowid > 0
begin
	update	t
	set t.ctr = '1'
	from @test as t
	inner join @stage as s on s.na = t.na and s.age = t.age
	where s.rowid = @rowid

	set @rowid = @rowid - 1
end

set rowcount 0

select * from @test

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources