need help in update statement using if

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 2008 Forums

Transact-SQL (2008)

need help in update statement using if

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-05 : 08:10:07

My tables and data as following,


declare @tApplicant table (myID varchar(200), myCategory varchar(10) null);
insert into @tApplicant(myID) values('1925');
insert into @tApplicant(myID) values('4474');
insert into @tApplicant(myID) values('8398');
insert into @tApplicant(myID) values('4656');
insert into @tApplicant(myID) values('2288');
insert into @tApplicant(myID) values('4876');
/* myID is unique */



declare @tMySPM table (myID varchar(200), subjectCd varchar(10));
insert into @tMySPM values('1925','99');
insert into @tMySPM values('1925','19');
insert into @tMySPM values('2288','78');
insert into @tMySPM values('2288','99');
insert into @tMySPM values('4656','72');
/* Combination of myID and subjectCd is unique */


declare @tMySTPM table (myID varchar(200), subjectCd varchar(10));
insert into @tMySTPM values('4474','99');
insert into @tMySTPM values('4474','19');
insert into @tMySTPM values('2288','78');
insert into @tMySTPM values('2288','99');
insert into @tMySTPM values('8398','72');
/* Combination of myID and subjectCd is unique */

What I would do
1. update @tApplicant set myCategory='A',
if myID exists in @tMySPM and @tApplicant(myCategory) is null
2. update @tApplicant set myCategory='B',
if myID exists in @tMySTPM and @tApplicant(myCategory) is null
3. update @tApplicant set myCategory=null,
if myID not exists in @tMySPM / @tSTPM and @tApplicant(myCategory) is null

My expected result,

myID  | myCategory
-----------------------------
1925	A
4474	B
8398	B
4656	A
2288	A
4876	NULL

Really need help. I'm stuck

So far, my T-SQL as following,

update @tApplicant 
set myCategory = 
(if exists(t2.myID) 
Begin
'A'
End
else if exists(t3.myID) 
Begin
'B'
End
else NULL 
end)

from @tApplicant t1
inner join @tMySPM t2 on t1.myID=t2.myID
inner join @tMySTPM t3 on t1.myID=t3.myID

parody
Posting Yak Master

111 Posts

Posted - 2012-04-05 : 10:06:21

A number of ways. Heres one.

UPDATE tA
SET tA.myCategory = 
	CASE 
		WHEN tM.myID IS NOT NULL THEN 'A'
		WHEN tMT.myID IS NOT NULL THEN 'B'
		ELSE NULL
	END
FROM @tApplicant tA
	LEFT JOIN @tMySPM tM
		ON tA.MyID = tM.MyID
	LEFT JOIN @tMySTPM tMT
		ON tA.MyID = tMT.MyID

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-05 : 11:21:51

tq sir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 12:01:39

[code]
update t
set myCategory = t1.Cat
from @tApplicant t
cross apply (select MAX('A') AS Cat
from @tMySPM
where MyID = t.MyID
UNION ALL
select MAX('B')
from @tMySTPM tMT
where MyID = t.MyID
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-04-07 : 02:40:05

tq mr visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:13:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 05:13:17

As Simple as this


Update @tApplicant
Set myCategory = Case
		   When myCategory IS NULL AND Exists(Select myID From @tMySPM Where myID = @tApplicant.myID)
		   Then 'A'
		   When myCategory IS NULL AND Exists(Select myID From @tMySTPM Where myID = @tApplicant.myID)
		   Then 'B'
		   Else NULL
		 End

Vinu Vijayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources