need help with update query - 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)

need help with update query

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2010-05-16 : 14:36:00

i have 2 tables as follows

tblEntity (entityID, mainContactID)

tblUser (userID, userType, entityID)

userType is an int field with values 1-9. if userType = 1 then they are a mainContact and i need to update the mainContactID field in tblEntity with the userID. so in pseudo code i need as follows

loop through tblUSer
if userType = 1 then update tblEntity set mainContactID = userID where tblEntity.entityID = tblUser.entityID

any help is appreciated. i could probably write a web page or simple program to do the loop but i figure it's easier to do it via a sql statement

cheers
neil

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-16 : 15:14:31

Try this:


UPDATE U
   SET U.mainContactID = E.userID
  FROM tblUser AS U
       INNER JOIN
       tblEntity AS E
       ON E.entityID = U.entityID
          AND U.userType = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:20:37

quote:

Originally posted by malpashaa

Try this:


UPDATE U E
   SET U E.mainContactID = E U.userID
  FROM tblUser AS U
       INNER JOIN
       tblEntity AS E
       ON E.entityID = U.entityID
          AND U.userType = 1

small typo in aliases

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

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-17 : 16:30:03

Yes you are right. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-18 : 01:09:32

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources