Replace string value to correspond with ID value

I'm in the process of converting an old database to a new SQL 2005 database.  The table I'm having trouble with is similar to this:

OrderDate           VendorName            Comments
10/2/2008           Some Company        Great comment.
9/30/2001           ABC Industries         More comments.
11/18/2004         Big Company            Terrible comment.

My goal is to create another table (called "Vendors") that contains all the different vendor names and gives each a unique ID:

VendorID           Name
1                        Some Company
2                        ABC Industries
3                        Big Company

I already have the new "Vendors" table populated with the vendor names, but I'm not sure how to then go back to the original table and replace the "VendorName" values with the new VendorID.  I know how to script something to perform this (in VB or PHP or something), but I'd like to do this with SQL.

Thanks in advance for any help, and let me know if you need clarification.
Who is Participating?
justin-clarkeConnect With a Mentor Commented:

SET tblOLD.VendorName = tblNEW.VendorID

 tblNew ON tblNEW.Name = tblOLD.VendorName

WHERE (tblNEW.Name = tblOLD.VendorName)

Possibly something like that from the top of my head.
wotechAuthor Commented:
Worked like a charm!  Thanks for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.