SQL Server 2005 Update Problem

Here is what i have... VS.NET / SQL Server 2005

Two tables: tblGenerators / tblCustomers

tblGenerators has a column tblGenerators.CustomerID (Stores a 4 charactor string i.e. 9999 which represents the customer account number)

tblCustomers has a column tblCustomers.CustomerID (is a unique RecID number) and tblCustomers.Account (4 charactor account number)

I want to convert(change) the tblGeneraotors.CustomerID( which stores a 4 charactor string i.e. 9999 which represents the customer account number) to the Unique rec id number from the customers table.

Below is the code I am attempting to use but having trouble.
Can someone tell me what I am doing wrong please.
I have have looked at W3 but minimal help there.



Dim qry As String = "Update tblgenerators "
qry += "SET tblgenerators.customerid = tblcustomers.customerid "
qry += "JOIN tblCustomers ON tblgenerators.customerid=tblcustomers.account "
qry += "WHERE ('tblgenerators.customerid' = tblcustomers.Customerid)"

It might be helpful if you post the error message that you received as well.

Since you are going to store a numeric value in the tblGenerators.CustomerID field, you should change the field's datatype to reflect that. Unfortunately it is not as simple as simply selecting the new datatype. First thing I would do is add a temporary numeric field, let's say TmpCustomerId, and set that field's value to the tblCustomers.CustomerId field. The update should look something like this :

UPDATE tblGenerators SET TmpCustomerId = (SELECT CustomerId FROM tblCustomers WHERE tblCustomers.Account = tblGenerators.CustomerId)

Now you can remove the tblGenerators.CustomerId field and add it again with the numeric datatype. Once that is done you can update the new CustomerId field with the value stored in the TmpCustomerId field and discard the temporary field.

It might be that there is one or more constraints on the tblGenerators.CustomerId field.
If there are anything other that a foreign key constraint to the tblCustomers.CustomerId field, you will have to find a way to add a similar constraint after the field's datatype has been changed. The current constraints will have to be removed before the field itself can be removed and added again. Once the field's datatype has been changed, it is a good idea to add a foreign key constraint to reinforce data integrity.