update fields based on a join - 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)

update fields based on a join

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-18 : 03:18:39

what's the correct way to update fields based on a join
my current code

update usercontacts left join sccontacts on usercontacts.id=sccontacts.usercontactsid left join shoppingcart on shoppingcart.cartitemid=sccontacts.cartitemid set userid=1 where userid is null and shoppingcart.usrsessid='218_78.46122'

is not working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 03:26:55

quote:
Originally posted by esthera

what's the correct way to update fields based on a join
my current code

update usercontacts left join sccontacts on usercontacts.id=sccontacts.usercontactsid left join shoppingcart on shoppingcart.cartitemid=sccontacts.cartitemid set userid=1 where userid is null and shoppingcart.usrsessid='218_78.46122'

is not working

change it like this:-

update uc
set uc.userid=1 
usercontacts uc  
left join sccontacts sc
on uc.id=sc.usercontactsid 
left join shoppingcart scr
on scr.cartitemid=sc.cartitemid 
where uc.userid is null 
and sc.usrsessid='218_78.46122'

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-18 : 03:36:46

Line 3: Incorrect syntax near 'usercontacts'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 04:05:56

quote:
Originally posted by esthera

Line 3: Incorrect syntax near 'usercontacts'.

update uc
set uc.userid=1
from usercontacts uc
left join sccontacts sc
on uc.id=sc.usercontactsid
left join shoppingcart scr
on scr.cartitemid=sc.cartitemid
where uc.userid is null
and sc.usrsessid='218_78.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources