nested subquery assistance please 2

[link9]

Hello --

I have two tables:
projectList
client

and currently, there is a string value (a last name) in projectList for the field, client (which is also the name of the other table) --

The client table has a clientID that corresponds to the projectList.client -- that field in the client table is called lName

So here's the breakdown:
client projectList
lName client
clientID

See where I'm going with this? I would like to do a global replace on the projectList table to replace those string values with the clientID that corresponds to it in the client table...

here's what I have that doesn't work --

UPDATE projectList
SET client=(SELECT clientID FROM client WHERE client.lName = projectList.client)


I'm using SQL Server 7.0 -- and would greatly appreciate any guidance anyone could provide --

thx
Paul Prewett

 

[grega]

I haven't tried this but it's worth a go ... you shouldn't need a nested select for this type of update. I'm sure of the impact of updating a join field though.

UPDATE projectList
SET client = clientID
FROM client c, projectList p
WHERE c.lName = p.client

Greg.

 

[link9]

Well, I'm just creating the database, so there will be no impact. In the old database, client was referred to by last name, but I have decided to give them ID's which will make my life a little easier, and then just reference to the client table if I need to see their name in a view or something.

Thanks for your suggestion -- it worked the trick.