Update 2 fields from different tables

[thorny00]

Thank you in advance for your time and help and sorry if this has been posted before.

I have one table - tbl_Members with 2 fields that need to be updated. The c_MemberID field is coming from RAW_Data. Is this even possible to do?


UPDATE tbl_Members
SET
OLD_MemberId = MemberID,
MemberID = c_MemberID,
SocialSecurityNumber = EmpSSN
FROM RAW_Data INNER JOIN (
SELECT c_MemberID, EmpSSN FROM RAW_ValueOptions_H1_2012
WHERE (FeedID = '30')
GROUP BY c_MemberID, EmpSSN
HAVING COUNT(*) >= 1) RAW_Data
ON RAW_Data.EmpSSN = tbl_Members.EmployeeID

 

[TamarGranor]

It looks like you're joining the derived table RAW_Data to itself. Try it like this:

UPDATE tbl_Members
SET
OLD_MemberId = MemberID,
MemberID = c_MemberID,
SocialSecurityNumber = EmpSSN
FROM tbl_Members INNER JOIN (
SELECT c_MemberID, EmpSSN FROM RAW_ValueOptions_H1_2012
WHERE (FeedID = '30')
GROUP BY c_MemberID, EmpSSN
HAVING COUNT(*) >= 1) RAW_Data
ON RAW_Data.EmpSSN = tbl_Members.EmployeeID

Tamar