SQL query

troyvw
troyvw used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
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.
Commented:
Are you saying that you need to have a list of values in the one field?


UPDATE tsr
SET [TRACKING NUMBER] =
  STUFF((SELECT ', ' + tn.[TRACKING NUMBER]
    FROM        
      CaseProfileDataTEST.dbo.[NEW BUIS SUPPLIES SHIPPING] ss
      INNER JOIN
      CaseProfileDataTEST.dbo.[NEW BUIS TRACKING NUMBERS] tn
         ON ss.[CASE ID NUM] = tn.[CASE ID NUM]
         AND ss.[ENROLLMENT BATCH] = tn.[ENROLLMENT BATCH]
         AND ss.[SHIP ID] = tn.[SHIP ID]
    WHERE ss.[SHIPPING TYPE INDICATOR] LIKE 'T'
    AND tn.SECTION LIKE 'TRSHIP'
    AND tsr.[ENROLLMENT BATCH] = ss.[ENROLLMENT BATCH]
    AND tsr.[CASE ID NUM] = ss.[CASE ID NUM]
    AND tsr.[DATE NEEDED ID] = ss.[DATE NEEDED ID]
    FOR XML PATH('')),1,2,'')
FROM dbo.[NEW BUIS TRAINING SUMMARY REPORT] AS tsr
;

--I think you mean =, not LIKE.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Rob, what does the for XML syntax do?

Commented:
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

Author

Commented:
Thank you! That is what I needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial