try it this way:

UPDATE _SMDBA_.ONCALL_ SET TierOne=1
WHERE _STAFF_ in (SELECT _STAFF_ FROM _SMDBA_.ONCALL_ WHERE _GROUP_=1040 AND TierOne=1)
AND _GROUP_=1047

What version of SQL? What data type is the MemberID field? In SQL2K, if the field is a numeric field (I usually use int or bigint for identity fields) then there should be no problem setting the MemberID field to be an identity field as long as all values are unique and not null. Can you provide more information about the table? Perhaps generate the sql script for the table including indexes, primary keys, foreign keys, defaults, and check con...