More SQL help!

Teledude · 2 Dec 2008 at 09:03

Trying to run this…

update Members

set StaffDept = newaddresstoimport.StaffDept

where Members.AlternateId = 20870824

getting this back…

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "newaddresstoimport.StaffDept" could not be bound.

Basically I want all the StaffDept ID's in the members table updated from the newaddresstoimport table where the AlternateId's in both match.

Just trying the above as a tester and no go yet

rpstewart · 2 Dec 2008 at 09:19

You need to be more specific then, you can't just put a random column name as the value in the set, it need to be either an explicit value or the result of another select statement:

Code:

update Members
set StaffDept = (select newaddresstoimport.StaffDept from newaddresstoimport where newaddresstoimport.AlternateId=Members.AlternateID)
where Members.AlternateId = 20870824

The syntax might not be perfect but you get the idea.

Teledude · 2 Dec 2008 at 09:27

Thanks

Im getting a wierd collation error now?!

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Teledude · 2 Dec 2008 at 09:45

sorted it thanks mate

one column has a different collation!