#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0

    Complex SQL update query


    Hello all - I am trying to write an update query that is much more complicated than anything I've done and could use some help.

    I have a primary table (TBL1) with the following relevant fields:

    ID
    Status
    DupeID
    OwnerID
    OtherOwners

    I have an additional table (TBL2) where the owners are defined:

    OwnerID
    Name

    I'm trying to build a query that will do the following:

    For all of the records in TBL1 where 'Status' = 'Primary', update the OtherOwners field with the 'Name' values (multiple values separated by a semicolon) of ALL of the records where
    DupeID of the other records is equal to the DupeID of the 'Primary' record.

    So - if I had the following values:

    TBL1:

    ID Status DupeID OwnerID OtherOwners
    1 Primary 234 3
    2 Secondary 234 1
    3 Secondary 234 2
    4 Secondary 567 2

    TBL2:

    OwnerID Name
    1 Joe
    2 Jane
    3 Jim


    I would want the query to result in an updated TBL1 that looks like this:

    ID Status DupeID OwnerID OtherOwners
    1 Primary 234 3 Joe; Jane
    2 Secondary 234 1
    3 Secondary 234 2
    4 Secondary 567 2

    As an added complication, I only want the value 'Joe' to appear once in the DupeOwners column regardless of how many 'secondary' records share the DupeID value of the primary record.

    After banging my head against the wall for quite awhile now, I think this is beyond my capabilities at the moment. Can anyone help?

    Thanks.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    Besides it is complex, it will be wrong to do this.
    You should never store several values in one field.

    Can you explain why you want to do this?

    You will be able to get the secondary names in a select statement, using JOIN and maybe also GROUP BY.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    I agree. Unfortunately these are just a few fields in a very large database that needs to be maintained as-is for downstream processing. Eventually this data needs to be exported from the DB along with other fielded data in a large CSV file. The final export is application-driven and I don't have control of it, so ultimately the data needs to get populated back into this DB so downstream users have it available.

    If it's easier to port the results to a CSV file that contains just the ID and the 'RelatedOwners' fields I can always overlay it back into the DB in a separate step, but I don't imagine that would make it any easier.
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    What I read, is that you have a application that create a CSV file and then put it back into the same DB to be accessible by "downstream" user?
    I think/hope I read it wrong, as it doesn't make any sense to me.

    Can see it is more complex than I even thought. Forgot that GROUP_CONCAT only works with MySql.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Thanks for the comments. The users perform their own ad-hoc exports of fielded data and I can't change the export process, so I need to actually put that data into a single field so it's available to them.
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    First, I must admit I am not expert in MS SQL, but please see if this will return the kind of information you are looking for (have not been able to test it):
    sql Code:
     
    SELECT 	TBL1.ID
    	, 	TBL1.OwnerID
    	, 	RelatedOtherOwner = STUFF((SELECT '; '+name FROM TBL2 WHERE TBL2.STATUS = 'Secondary' AND TBL2.DupeID=TBL1.DupeID AND  FOR XML PATH('')) , 1 , 1 , '' )
    FROM TBL1
    WHERE TBL1.STATUS = 'Primary'
    Last edited by MrFujin; January 28th, 2013 at 04:14 PM.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    This is not quite right, as some of the fields are associated with the wrong tables (i.e. 'Status' is a tbl1 field not tbl2, and tbl2 does not have a DupeID field). But I was unaware of the 'STUFF' statement, which looks like it could be helpful in doing what I need. I'm going to try to simplify the query and play around with the STUFF statement to see if I can get close and will post back. Thanks.
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    Can see I did managed to messed up when I tried to simplify it.

    My idea was to make a self-join, to get the name with the primary part:
    Code:
    TBL3
    ID	Status		DupeID	OwnerID	OtherOwners	Name
    1	Primary		234	   3			Joe
    1	Primary		234	   3			Jane
    sql Code:
     
    SELECT T1a.*, T1b.name
    FROM TBL1 T1a
    INNER JOIN TBL1 T1b ON T1b.DupeID = T1a.DupeID 
    WHERE T1a.STATUS = 'primary' AND T1b.STATUS = 'Secondary'


    And then from this result, try to make a SELECT around this using STUFF, which should simulate/replace the GROUP_CONCAT function from MySql.
    As you said, it is complex.

IMN logo majestic logo threadwatch logo seochat tools logo