Updating one table with data from another.... 1

[StevenK]

I have a SQL server 7.0 database. Within it I am construsting a table with information to be used in a report (Crystal Reports). In order to make this as simple as possible I am putting all the required information into a single table. What I initially do is feed the key information into this table - for instance CustomerID, UserID. I then use code in my application to cycle through this data and update the entries according to the key information - i.e. I update the entry with UserName, UserSurname according to the UserID entry found. Similarly I update CustomerName, CustomerAddress1, CustomerAddress2 according to the entry in the CustomerID field. This is al done through the program and is taking some time. Is there a query I can run that will do this task for me.
I'm looking for something like :
UPDATE [RptTable] RT
SET RT.[CustomerName] = (SELECT C.[CustomerName] FROM [Customers] C WHERE C.[CustomerID] = RT.[CustomerID]),
RT.[CustomerAddress1] = (SELECT C.[CustomerAddress1] FROM [Customers] C WHERE C.[CustomerID] = RT.[CustomerID]),
RT.[CustomerAddress2] = (SELECT C.[CustomerAddress2] FROM [Customers] C WHERE C.[CustomerID] = RT.[CustomerID]), ...
...and so on.
Is this the way to go about things - my impression of this is that I'm going to be accessing the [Customers] table more than once and will slow things down - is there a way to get the required information (on one foul swoop) [CustomerName], [CustomerAddress1], [CustomerAddress2], etc. from the [Customers] table and use the [CustomerID] link to perform the UPDATE.
It could be that I'm missing the obvious on this one ....
Can anyone offer me a pointer or two ?
Thanks in advance.
Steve

 

[tlbroadbent]

Try this...

UPDATE RptTable
SET CustomerName = C.CustomerName,
CustomerAddress1 = C.CustomerAddress1,
CustomerAddress2 = C.CustomerAddress2, ...

FROM RptTable RT INNER JOIN Customers C
ON RT.CustomerID = C.CustomerID
Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[StevenK]

Thanks Terry. I will put this into practice when I return to the office tomorrow - it should speed the process up.
Thanks again.
Steve

 

[StevenK]

OK - This has given me the required results - can anyone now help me a little further and extend the logic on this such that I am updating my report table [RptTable] from two other tables [Customers] and [Users] if I have links through [CustomerID] and [UserID] ?
That is according to the [CustomerID] entry I update the [CustomerAddress1], [CustomerAddress2], [CustomerAddress3] fields according to the [CustomerID] and similarly update the [UserName] and [UserSurname] according to the [UserID] entry. Is this possible using JOINS in the same manner as the previous response ?
Thanks in advance once more ...
Steve

 

[tlbroadbent]

You can do what you've asked. Just add another JOIN. This assumes that there will always be a related record in Customers and Users for each record in RptTable.

UPDATE RptTable SET
CustomerName = C.CustomerName,
CustomerAddress1 = C.CustomerAddress1,
CustomerAddress2 = C.CustomerAddress2,
UserName = U.UserName,
UserSurname = U.UserSurname, ...

FROM RptTable RT
INNER JOIN Customers C
ON RT.CustomerID = C.CustomerID
INNER JOIN Users U
ON RT.UserID = U.UserID Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[StevenK]

Thanks again Terry. Much appreciated for the assistance.

 

[StevenK]

Another addition I'm afraid. Suppose I have an additional table - call it [PaymentDescription] that links to the [Customers] table through a [PaymentCode] field.
So we have [CustomerID] in the table [RptTable], [CustomerID], [PaymentCode] in the [Customers] table and [PaymentCode], [PaymentDescription] in the [PaymentTypes] table. Is there a way in which I can run an update similar to before such that I feed in [PaymentDescription] from the [PaymentTypes] table according to the [PaymentCode] link from this table to [Customers] and then through the [CustomerID] link from the [Customers] table to the target [RptTable]. Is this acheivable or does this become too complex ?
Thanks again in advance.
Steve.

 

[tlbroadbent]

It is doable.

UPDATE RptTable SET
CustomerName = C.CustomerName,
CustomerAddress1 = C.CustomerAddress1,
CustomerAddress2 = C.CustomerAddress2,
UserName = U.UserName,
UserSurname = U.UserSurname,
PaymentDescription = P.PaymentDescription ...

FROM RptTable RT
INNER JOIN Customers C
ON RT.CustomerID = C.CustomerID
INNER JOIN Users U
ON RT.UserID = U.UserID
INNER JOIN PaymentTypes P
ON C.PaymentCode = P.PaymentCode

NOTE: This only works if all the tables contain records matching the JOIN relationships. For example, if there is no matching PaymentType record, none of the fields will be updated. If the relationships always exist, then happy computing. Otherwise you'll need to consider OUTER JOINS. The query becomes slightly more complex and proper structure of the query becomes more critial. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[StevenK]

Thanks again Terry. I actually resolved the question prior to checking for your reply. Finally got my head round it now.
Thanks for your continued help with these questions ...
Steve