update using a lookup

[snowneil]

Hi,

I have an old database layout that is still being used and i need to change the design.

I have 2 tables.

COM, USERS.

COM has a 'managedBy' field which contains a foreign key from USERS.

USERS has the following fields,
'id','username','access','active'.

At the moment USERS.id is a code, ie. J1S.
This code is going into COM.managedBy.

I want to change USERS.id to USERS.code and add a new field called USERS.id which will be an id number used as its primary key.

That bit is simple enough to change but what i am asking is this.

Is there a way to write 1 SQL statement to update all the records with a value in COM.managedBy to its relevant USERS.id by doing a lookup on the id and code in USERS.

I need to update it quickly and updating each different code separately seems tedious.

If that doesn't make sense i will give a bit more detail.

 

[bborissov]

UPDATE Table COM SET managedBy = (SELECT Id FROM Users WHERE Code = COM.managedBy)

or

UPDATE COM SET managedBy = Users.Code
FROM COM
INNER JOIN Users ON Com.managedBy = Users.Code
-- not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

Oops. last one must be:

UPDATE COM SET managedBy = Users.Id
FROM COM
INNER JOIN Users ON Com.managedBy = Users.Code

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[snowneil]

I figured it out and wrote this before your replies.

UPDATE COM
SET managedBy = USERS.id
FROM (SELECT id, code FROM USERS) AS USERS
WHERE COM.managedBy = USERS.code

I take it yours works as well but i have nothing to test it on now..

Thanks for the help