asked on

SQL query

In the query listed bellow I need to update the Tracking Number field with the values in the Tracking number 2 field where the ship id is the same. Can I do that in a SQL query? I am trying to run it in sql server 2008.

SELECT     TOP (100) PERCENT dbo.[NEW BUIS TRAINING SUMMARY REPORT].[CASE ID NUM],                                       
                      dbo.[NEW BUIS TRAINING SUMMARY REPORT].[TRACKING NUMBER], dbo.[NEW BUIS TRAINING SUMMARY REPORT].[SHIPPED DATE],                        
                      CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[TRACKING NUMBER] AS TrackingNumber2, 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIP ID]
FROM         dbo.[NEW BUIS TRAINING SUMMARY REPORT] INNER JOIN
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING] ON 
                      dbo.[NEW BUIS TRAINING SUMMARY REPORT].[ENROLLMENT BATCH] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[ENROLLMENT BATCH]
                       AND dbo.[NEW BUIS TRAINING SUMMARY REPORT].[CASE ID NUM] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[CASE ID NUM] AND
                       dbo.[NEW BUIS TRAINING SUMMARY REPORT].[DATE NEEDED ID] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[DATE NEEDED ID] INNER
                       JOIN
                      CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS] ON 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[CASE ID NUM] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[CASE ID NUM]
                       AND 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[ENROLLMENT BATCH] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[ENROLLMENT BATCH]
                       AND 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIP ID] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[SHIP ID]
WHERE     (CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIPPING TYPE INDICATOR] LIKE 'T') AND 
                      (CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].SECTION LIKE 'TRSHIP')
ORDER BY CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIP ID]

Open in new window

brad2575

should be almost as simple as this (may need to tweak it a bit but this logic is how you would do it).

Update TableNamehere
Set Tracking Number = Tracking Number 2
FROM         dbo.[NEW BUIS TRAINING SUMMARY REPORT] INNER JOIN
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING] ON 
                      dbo.[NEW BUIS TRAINING SUMMARY REPORT].[ENROLLMENT BATCH] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[ENROLLMENT BATCH]
                       AND dbo.[NEW BUIS TRAINING SUMMARY REPORT].[CASE ID NUM] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[CASE ID NUM] AND
                       dbo.[NEW BUIS TRAINING SUMMARY REPORT].[DATE NEEDED ID] = CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[DATE NEEDED ID] INNER
                       JOIN
                      CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS] ON 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[CASE ID NUM] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[CASE ID NUM]
                       AND 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[ENROLLMENT BATCH] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[ENROLLMENT BATCH]
                       AND 
                      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIP ID] = CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].[SHIP ID]
WHERE     (CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING].[SHIPPING TYPE INDICATOR] LIKE 'T') AND 
                      (CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS].SECTION LIKE 'TRSHIP')

Open in new window

troyvw

ASKER

the problem is that ship id can be the same for several different records. I might have three records with a ship record id of 12 for example and they ALL have to be added to the Tracking Number field. with this syntax i think it would probable just replace the last one.

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

troyvw

ASKER

Rob, what does the for XML syntax do?

Rob Farley

That does the string concatenation. Take a set of strings and turns it into XML - but if you dont' have a column name, and don't use a specific PATH string, then it doesn't put any tags in and just does the concatenation.

Rob

troyvw

ASKER

Thank you! That is what I needed.