How to update this? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to update this?

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-20 : 02:49:08

I have this tables.

declare @tbl as table(id int)
insert into @tbl
select 1 union all
select 2 union all
select 9 union all
select 4 union all
select 5

declare @tbl1 as table(id int,names varchar(50))
insert into @tbl1
select 1,'sac'union all
select 7,'gir' union all
select 10,'vip'union all
select 2,'fat' union all
select 3,'lei'

I would like to update @tbl1 id with the ids in @tbl which are not in @tbl.For example in @tbl1 there is an id 7 which is not existing in @tbl so it should be updated with id 5 because 5 is nearest id to 7.Same way for id 10 in @tbl1.It should be updated with id 9 from @tbl because it is the nearest id for 10.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 02:59:55

[code]Update t1
set t1.id= b.maxidvalue
FROm @Tbl1 t1
left join @tbl t
ON t1.id=t.id
outer apply(select max(id) as maxidvalue
from @tbl
where id<t1.id)b
where t.id is null[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-20 : 03:28:28

Vishakh you are amazing.
How are you able to do this so quickly?
Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources