November 21, 2006 at 1:33 am
Hi There,
Is it possible to run an update statement on a table using a left outer join ? If so, what would your syntax look like ?
This is my original select statement :-
select * from calllog left outer join asgnmnt on asgnmnt.callid=calllog.callid
where asgnmnt.assignee='Hanli Burger' and calllog.callstatus='Pending'
I need to update a field on the asgnmnt table where the assignee is Hanli Burger and the call status is pending. The call status field is on the calllog table and the assignee field is on the asgnmnt table.
Thanks,
Sonia
November 21, 2006 at 1:57 am
select *
from calllog T1
left outer join asgnmnt T2
on T2.callid=T1.callid
where T2.assignee='Hanli Burger'
and T1.callstatus='Pending'
Update T1
set colx = case when T2.coly is null then colx else T2.coly end
from calllog T1
left outer join asgnmnt T2
on T2.callid=T1.callid
where T2.assignee='Hanli Burger'
and T1.callstatus='Pending'
Because you are using a where-clause refering a non-null column of T2 (asgnmnt) you will actualy toutch the same rows as with an inner join. Use the Innerjoin !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply