help with query...rewrite - 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 2000 Forums

Transact-SQL (2000)

help with query...rewrite

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-11-26 : 14:58:00

Declare @tbl1 table(id int IDENTITY(1,1), VALIDX varchar(20))
Declare @tbl2 table(id int )

insert into @tbl1 select 'X' union all
select 'X' union all
select 'N' union all
select 'N' union all
select 'X' union all
select 'X' union all
select 'N' union all
select 'X' union all
select 'N'

insert into @tbl2 select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6

--RESET TO DEFAULT 'N'
UPDATE @TBL1 SET VALIDX='N'

--UPDATE TO 'X' IF MATCH
UPDATE A SET VALIDX='X'
FROM @TBL1 A
INNER JOIN @TBL2 B
ON A.ID=B.ID

as you can see I'm writing this in two parts, I know there is a much better way to rewrite this in a single update statement. Can someone point me to the right direction?

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 15:04:15

UPDATE A
SET VALIDX = (Case when A.ID = B.ID then 'X' else 'N' end)
FROM @TBL1 A
left outer JOIN @TBL2 B
ON A.ID=B.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources