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.

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.

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.

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.

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.

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

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.

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:



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.