SQL Statement Help to Normalize a DB

Using Query to replace descr with id

[Argblat]

In attempting to clean up a poorly designed database structure I've run into a snag. I will try to use a simplified example to help illustrate:

TABLE ACCOUNTS [acctID, acctRep]
1 ¦ BOB
2 ¦ SALLY
3 ¦ JIM
4 ¦ BOB

In order to normalize the table I moved the Account Rep's to their own table as such:

TABLE ACCOUNT_REP [repID, repName]
1 ¦ BOB
2 ¦ SALLY
3 ¦ JIM

I now need to, using update query, update table ACCOUNTS to change acctRep from a name to an ID. Can someone please help me with the query using the newly created link table to update the master table?

-Mike

[Argblat]

I got it,

UPDATE ACCOUNTS
SET ACCOUNTS.acctRep = ACCOUNT_REP.repID
FROM ACCOUNTS
INNER JOIN ACCOUNT_REP
ON ACCOUNT_REP.repName = ACCOUNTS.acctRep

[syber]

This would be the ANSI syntax

UPDATE ACCOUNTS  
SET acctRep = (SELECT repID FROM ACCOUNT_REP 
     WHERE repName = ACCOUNTS.acctRep)  

[piatkow]

In a business environment I wouldn't risk making that change on the fly. Output to a temporary table and eyeball the results first. Once you are happy that there are no problems delete the original and rename the temporary table.