Find sets with duplicate fields and merge them in the same table

Question

I'm sure that there may be an answer out there but I've been searching for a month now and can't find how to do it.

I have a table for translations with: ID, Resource_Object, Resource_Name, Resource_Value, Culture_name, idMinCopy.

New objects appear automatically when the page is changed and a function checks if another object has an identical translation to copy it to the new object.

What I need is an SQL that allows me to check the existing objects and copy languajes between objects with same Resource_Value when Culture_name is either 'ES', 'EN' or 'Neutral'

Neutral is an in-page ID that can be unique for every culture_name, identic to 'EN' or identic to 'ES'. Is the only Culture_Name that is guaranteed to exist as it's auto-generated.

I've put up a sql fiddle with the test table: http://sqlfiddle.com/#!3/d1f52/1

Example:

ID  Resource_Object  Resource_Name  Resource_Value Culture_Name idMinCopy
--------------------------------------------------------------------------
1  |home.aspx       |label1.text   |Blue          |Neutral     |Null
2  |home.aspx       |label1.text   |Blue          |EN          |Null
3  |home.aspx       |label1.text   |Azul          |ES          |Null
4  |home.aspx       |label1.text   |Bleu          |FR          |Null
5  |page1.aspx      |lblColor.text |Azul          |Neutral     |Null
6  |page1.aspx      |lblColor.text |Blue          |EN          |Null
7  |page1.aspx      |lblColor.text |Azlu          |ES          |Null
8  |page1.aspx      |lblColor.text |Blau          |CAT         |Null
9  |page1.aspx      |lblTitle.text |Color         |Neutral     |Null
10 |page1.aspx      |lblTitle.text |Color         |ES          |Null
11 |page1.aspx      |lblTitle.text |Colour        |EN         |Null

This is a small sample, we can have many duplicates and different languajes. The value that should prevail is always the one with the lower [id]. As the only guaranteed existing [culture_name] for any object is 'neutral', what I do is compare 'neutral' [Resource_Value] with all other 'Neutral', 'ES' and 'EN' [Resource_Value] I've managed to get the 'CAT' values in the first object but not sure of how to put the 'FR' in the second object.

Desired Result:

ID  Resource_Object  Resource_Name  Resource_Value Culture_Name idMinCopy
--------------------------------------------------------------------------
1  |home.aspx       |label1.text   |Blue          |Neutral     |1
2  |home.aspx       |label1.text   |Blue          |EN          |2
3  |home.aspx       |label1.text   |Azul          |ES          |3
4  |home.aspx       |label1.text   |Bleu          |FR          |4
5  |page1.aspx      |lblColor.text |Blue          |Neutral     |1
6  |page1.aspx      |lblColor.text |Blue          |EN          |2
7  |page1.aspx      |lblColor.text |Azul          |ES          |3
8  |page1.aspx      |lblColor.text |Blau          |CAT         |12
9  |page1.aspx      |lblTitle.text |Color         |Neutral     |Null
10 |page1.aspx      |lblTitle.text |Color         |ES          |Null
11 |page1.aspx      |lblTitle.text |Colour        |EN          |Null
12 |home.aspx       |label1.text   |Blau          |CAT         |12
13 |page1.aspx      |lblColor.text |Bleu          |FR          |4

I want to put in the [idMinCopy] field the ID of the lowest identical object. Later if a change is made in any 'Resource_value' I can easily update all with the same 'idMinCopy'.

I guess I need a recursive cte and a Merge statement to achieve this but I'm not able to make it work and not know what else to do.

Resources I've checked that may be related (Shown as code as I've no rep.):

http://stackoverflow.com/questions/21216534/sql-query-result-from-multiple-tables-without-duplicates

http://stackoverflow.com/questions/12910287/merging-tables-with-duplicate-data

http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example

http://www.codeproject.com/Articles/683011/How-to-use-recursive-CTE-calls-in-T-SQL

http://stackoverflow.com/questions/1222581/how-get-the-t-sql-code-to-find-duplicates

http://pratchev.blogspot.com.es/2008/03/upsert-and-more-with-merge.html

http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx

The sql I'm using now :

--- Search for similar 'Resource_Values' 
--- returns identical object with lowest ID
DECLARE @textoprueba as nvarchar(1000)
set @textoprueba='Blue'
select top 1 
case when t2.id is not null then t1.id end as t2id 
from tbtraducciones t1 LEFT join  tbtraducciones t2
on t1.resource_value = t2.resource_value AND (t1.culture_name='neutral' OR    t1.culture_name='en' OR t1.culture_name='es') 
AND t2.culture_name='neutral' AND (t1.Resource_name != t2.Resource_NAME OR t1.RESOURCE_OBJECT != t2.RESOURCE_OBJECT) 
where case when t2.id is not null then t2.id end is not null
and t2.resource_value=@textoprueba order by t1.id

--- Search for the ID of the 'Neutral' value for that object
DECLARE @minID as int
set @minID=[Result from previous SQL Statement]
select id from tbTraducciones 
where resource_name = (select resource_name from tbtraducciones where id=@minId) 
and resource_object= (select resource_object from tbtraducciones where id=@minId)
and culture_name = 'neutral'


---UPSERT DUPLICATES
DECLARE @idNeutral as int, @newId as Int
set @newID=[ID of the object with same 'Resource_Value' with higher ID]
set @idNeutral= [ID of the lowest id 'neutral' that has the same 'Resource_Value' in 'Neutral', 'ES' or 'EN']
;WITH T AS 
(
select 
t1.Resource_object , t1.Resource_name as Resource_Name, t3.Resource_Value AS     RESOURCE_VALUE, 
t3.Culture_Name AS CULTURE_NAME, t3.Id AS idMinCopy
from tbTraducciones t1 
JOIN tbTraducciones t2 on t2.id=@idNeutral 
right join tbTraducciones t3 on t3.Resource_Object = t2.Resource_Object AND t3.Resource_Name = t2.Resource_Name 
where t1.id=@newID and 
t3.culture_NAME <> 'Neutral'
)
UPDATE tbTraducciones SET
[RESOURCE_VALUE]=T.Resource_Value ,
[idMinCopy] = T.idMinCopy
FROM T
WHERE T.Resource_Object = tbTraducciones.Resource_Object
AND
tbTraducciones.Resource_Name = T.Resource_Name
AND
tbTraducciones.Culture_Name = T.Culture_Name
IF @@ROWCOUNT=0
INSERT INTO [tbTraducciones] (Resource_Object, Resource_Name, Resource_Value,     Culture_Name, idMinCopy)  
select t1.Resource_object, t1.Resource_name, t3.Resource_Value, t3.Culture_Name, t3.Id 
from tbTraducciones t1 
JOIN tbTraducciones t2 on t2.id=@idneutral 
join tbTraducciones t3 on t3.Resource_Object = t2.Resource_Object AND t3.Resource_Name =    t2.Resource_Name
where t1.id=@newID and t3.Culture_Name !='Neutral'

I'm absolutly sure that this has been resolved before somewhere, but I've been unable to find or adopt the solutions found.

Thank you for your Help.

I'd be extremely cautious here - beware of homonyms. Just because two items have been translated to the same string of characters in two places, doesn't mean that they have exactly the same meaning, and thus a change that's appropriate to one may not be appropriate to the other. — Damien_The_Unbeliever, Apr 11 '14 at 08:52
Sorry there is too much data so a little difficult to understand here. Let me summarize your problem here. You want an id for Resource_Value and Culture_Name combination. If this combination repeats you want to give the same id to the new combination else you want to generate a new id. Am I right here or am I missing something? — samar, Apr 11 '14 at 09:30
@Damien_The_Unbeliever Thanks for the hint, I'm using the Neutral to avoid this problem. Once the auto-matching of strings is done I can manually edit the neutral to avoid matching in the future. When I say manually I mean that I have a back-office screen where I can check one object and say to not auto-match it. English is not my languaje so maybe I'm not explaining it correctly. Thank you for your help! — Capgros, Apr 11 '14 at 09:49
@samar I want an id for Resource_Value and Culture_name='Neutral' . If this combination repeats or a combination of this Resource_Value and Culture_Name='ES' or Culture_Name='EN' then I want to Update the newest Object (Object is same RESOURCE_NAME and RESOURCE_OBJECT, many CULTURE_NAMEs) with all the values from oldest Object. And Insert missing RESOURCE_VALUES with it's CULTURE_NAME in both objects. Newest object is the one with the higher ID in it's NEUTRAL value. — Capgros, Apr 11 '14 at 10:01
@samar I'm possibly over thinking all this, as I'm not a database specialist and maybe there's another solution I haven't thought off. Feel free to suggest any other direction for the solution. — Capgros, Apr 11 '14 at 10:03
A little confusion here. In your "desired result" table, you have given "idMinCopy" as "1" to different "Resource_Value". Is this a mistake or am I missing something here? — samar, Apr 11 '14 at 10:42
@samar I've updated the description (I know, a lot of text, sorry about that) The "idMinCopy" field should store the ID of the lowest matching field. If the Field ID and idMinCopy are the same it means it is the lowest ID for that string. In the traductions page SELECT I just ask for items with "idMinCopy"="ID" or "idMinCopy"=NULL . So I save bandwith and resources (I'm absolute beginner with SQL so it's just a guess). And in the UPDATE statement when a text is changed I update all Fields with the same "idMinCopy". — Capgros, Apr 11 '14 at 11:15
My question still remains. In the rows with ID 1 and 5, "idMinCopy" value is "1" even when values of the column "Resource_Value" is different. Is it that since it is "Neutral" it should have "idMinCopy" value as "1" irrespective of values of the column "Resource_Value"? — samar, Apr 11 '14 at 11:46
@samar You are absolutely right. It's my mistake. Row 5 should have the value updated from row 1 value. I've updated it. — Capgros, Apr 11 '14 at 12:16

score 0 · Answer 1 · answered Apr 11 '14 at 13:57

This is as ugly as it gets. My solution consist of as many as 3 loops and might be horrible in terms of performance but I was able to get the expected result.

Also not sure if you will be able to understand my solution but I have tried adding comments wherever possible.

--simulation of actual table
DECLARE @tbTraducciones TABLE (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Resource_Object] [nvarchar](255) NULL,
    [Resource_Name] [nvarchar](128) NULL,
    [Resource_Value] [nvarchar](1000) NULL,
    [Culture_Name] [nvarchar](50) NULL,
    [idMinCopy] [int] NULL
    )

-- values for testing
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES (
'home.aspx','label1.text','Blue','Neutral',NULL);

INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('home.aspx','label1.text','Blue','EN',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('home.aspx','label1.text','Azul','ES',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('home.aspx','label1.text','Bleu','FR',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('page1.aspx','lblColor.text','Azul','Neutral',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('page1.aspx','lblColor.text','Blue','EN',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('page1.aspx','lblColor.text','Azul','ES',NULL);
INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name,idMinCopy) 
VALUES ('page1.aspx','lblColor.text','Blau','CAT',NULL);


--Solution starts from here
DECLARE @StartCount INT, @MaxCount INT
DECLARE @Resource_Value NVARCHAR(128), @Culture_Name NVARCHAR(128), @previdMinCopy INT,
@Resource_Object NVARCHAR(255), @Resource_Name NVARCHAR(128)

DECLARE @NewStartCount INT
DECLARE @NewResource_Object NVARCHAR(255),  @NewResource_Name NVARCHAR(128)

DECLARE @FirstResource_Object NVARCHAR(255),    @FirstResource_Name NVARCHAR(128)

SELECT @FirstResource_Object = [Resource_Object],   
        @FirstResource_Name = [Resource_Name]
    FROM @tbTraducciones
    WHERE ID = 1

SELECT @StartCount = 1, @MaxCount = MAX(ID) FROM @tbTraducciones

--This loop will insert all the records which are present in one [Resource_Object] -- [Resource_Name] combination 
--but not present in the other
--excluding Culture_Name as 'neutral'
WHILE(@StartCount <= @MaxCount)
BEGIN
    SELECT @Resource_Object = [Resource_Object],    
                @Resource_Name = [Resource_Name],
                @Resource_Value = [Resource_Value],
                @Culture_Name = [Culture_Name]
    FROM @tbTraducciones
    WHERE ID = @StartCount

    IF(@Culture_Name != 'neutral')
    BEGIN
        SET @NewStartCount = 1

        WHILE (@NewStartCount <= @MaxCount)
        BEGIN
            IF(@NewStartCount != @StartCount)
            BEGIN
                IF NOT EXISTS(SELECT 0 FROM @tbTraducciones
                                WHERE [Resource_Object] = @Resource_Object
                                AND [Resource_Name] = @Resource_Name
                                AND ID = @NewStartCount)
                BEGIN
                    SELECT @NewResource_Object = [Resource_Object], 
                        @NewResource_Name = [Resource_Name]
                    FROM @tbTraducciones
                    WHERE ID = @NewStartCount



                    IF NOT EXISTS(SELECT 0 FROM @tbTraducciones
                                WHERE [Resource_Value] = @Resource_Value
                                AND [Culture_Name] = @Culture_Name
                                AND [Resource_Object] = @NewResource_Object
                                AND [Resource_Name] = @NewResource_Name)
                    BEGIN
                        INSERT INTO @tbTraducciones (Resource_Object,Resource_Name,Resource_Value,Culture_Name)
                        VALUES (@NewResource_Object, @NewResource_Name, @Resource_Value, @Culture_Name)

                        UPDATE @tbTraducciones
                        SET [idMinCopy] = ID
                        WHERE ID IN (SELECT MAX(ID) FROM @tbTraducciones)
                        AND [Resource_Object] = @FirstResource_Object
                        AND [Resource_Name] = @FirstResource_Name
                    END
                END
            END

            SET @NewStartCount = @NewStartCount + 1
        END
    END

    SET @StartCount = @StartCount + 1
END

SELECT @StartCount = 1, @MaxCount = MAX(ID) FROM @tbTraducciones

--This loop will start updating "idMinCopy" for each row based on the logic
WHILE(@StartCount <= @MaxCount)
BEGIN
    SELECT @Resource_Value = [Resource_Value],
        @Culture_Name = [Culture_Name]
    FROM @tbTraducciones
    WHERE ID = @StartCount

    IF(@Culture_Name = 'neutral')
    BEGIN
        IF NOT EXISTS(SELECT 0 FROM @tbTraducciones 
                        WHERE [Culture_Name] = @Culture_Name
                        AND ID < @StartCount
                        AND idMinCopy IS NOT NULL)
        BEGIN
            UPDATE @tbTraducciones
            SET idMinCopy = @StartCount
            WHERE ID = @StartCount
        END
        ELSE
        BEGIN
            SELECT @previdMinCopy = MIN(idMinCopy)
            FROM @tbTraducciones
            WHERE [Culture_Name] = @Culture_Name
            AND ID < @StartCount
            AND idMinCopy IS NOT NULL

            UPDATE @tbTraducciones
            SET idMinCopy = @previdMinCopy
            WHERE ID = @StartCount
        END
    END
    ELSE
    BEGIN
        IF NOT EXISTS(SELECT 0 FROM @tbTraducciones 
                        WHERE [Resource_Value] = @Resource_Value
                        AND [Culture_Name] = @Culture_Name
                        AND idMinCopy IS NOT NULL)
        BEGIN
            UPDATE @tbTraducciones
            SET idMinCopy = @StartCount
            WHERE ID = @StartCount

        END
        ELSE
        BEGIN
            SELECT @previdMinCopy = MIN(idMinCopy)
            FROM @tbTraducciones
            WHERE [Resource_Value] = @Resource_Value
            AND [Culture_Name] = @Culture_Name
            AND idMinCopy IS NOT NULL

            UPDATE @tbTraducciones
            SET idMinCopy = @previdMinCopy
            WHERE ID = @StartCount

        END
    END

    SET @StartCount = @StartCount + 1

END


SELECT * FROM @tbTraducciones

Hope this helps.

Some side effects thought. All the rows without equivalent match got idMinCopy = "lower existing id" and the corresponding insert row. I've updated the SQL FIDDLE http://sqlfiddle.com/#!3/9e361/1 to put some not matching data so you can see it'e effects. Anyway it's the most aproximated to a solution I've got till now. — Capgros, Apr 14 '14 at 07:55
I think sqlfiddle.com is not working. Can you update your question with the scenario? I will try and update my answer accordingly. Also if you have found my answer helpful then please upvote it. :) — samar, Apr 14 '14 at 12:42
I can see the fiddle now. So can you please explain where is the problem? Please tell me the IDs which are having problem as per your fiddle so that I can analyse it and change my code accordingly. — samar, Apr 15 '14 at 04:18
I can't upvote because I still have no rep. Sorry. I've updated the scenario so you see what I mean. Again, thank you for your help! — Capgros, Apr 15 '14 at 07:07
In your updated question, why ids 9, 10 and 11 are having idMinCopy as Null in the desired results table? Is that the edit? Please write "EDIT" in place where you have edited the question as there is lot of text and searching for new text is very difficult. — samar, Apr 15 '14 at 08:44
They have idMinCopy as Null because there is no match with any other field. This is my first question here and trying to be clear I've written to much text... — Capgros, Apr 15 '14 at 15:11

Find sets with duplicate fields and merge them in the same table

1 Answers1