SQL scripts to find and correct duplicate attribute names

MSSQL - find duplicate attribute names

-- find all duplicate attributes in the key language
DECLARE @KeyLanguageID BIGINT
SET @KeyLanguageID = 9 -- TODO: change to the repository key language ID (9 = en_US, 7 = de_DE)
SELECT AR.[ArticleID] AS [ArticleID], AR.[Identifier] AS [ItemNo], AR.[CatalogID], AR.EntityID AS [Article.EntityID], AA.[ArticleRevisionID], AAL.ID AS [ArticleAttributeLangID], AAL.[Name]
FROM [ArticleRevision] AR
  INNER JOIN [ArticleAttribute] AA
    ON AR.[ID] = AA.[ArticleRevisionID]
    AND AR.[DeletionTimestamp] = AA.[DeletionTimestamp]
  INNER JOIN [ArticleAttributeLang] AAL
    ON AA.[ID] = AAL.[ArticleAttributeID]
    AND AA.[DeletionTimestamp] = AAL.[DeletionTimestamp]
  INNER JOIN
  (
    SELECT DISTINCT XAA.[ArticleRevisionID] AS [ArticleID], XAAL.[Name] AS [Name], MAX(XAA.[ID]) AS [MaxArticleAttributeID]
    FROM [ArticleAttributeLang] XAAL
      INNER JOIN [ArticleAttribute] XAA
        ON XAA.[ID] = XAAL.[ArticleAttributeID] 
        AND XAA.[DeletionTimestamp] = XAAL.[DeletionTimestamp]
        AND XAAL.[LanguageID] = @KeyLanguageID
    WHERE XAAL.[LanguageID] = @KeyLanguageID
    GROUP BY XAA.[ArticleRevisionID], XAAL.[Name]
    HAVING COUNT(*) > 1
  ) x
    ON x.[ArticleID] = AA.[ArticleRevisionID] 
    AND x.[Name] = AAL.[Name] 
    AND x.[MaxArticleAttributeID] <> AA.ID
WHERE AAL.[LanguageID] = @KeyLanguageID
ORDER BY AR.[CatalogID], AR.[Identifier], AAL.[Name];
GO
 
 

Oracle - find duplicate attribute names

-- find all duplicate attributes in the key language
SELECT AR."ArticleID" AS "ArticleID", AR."Identifier" AS "ItemNo", AR."CatalogID", AR."EntityID" AS "Article.EntityID", AA."ArticleRevisionID", AAL.ID AS "ArticleAttributeLangID", AAL."Name"
FROM "ArticleRevision" AR
  INNER JOIN "ArticleAttribute" AA
    ON AR."ID" = AA."ArticleRevisionID"
    AND DECODE( AR."DeletionTimestamp", AA."DeletionTimestamp", 1, 0 ) = 1
  INNER JOIN "ArticleAttributeLang" AAL
    ON AA."ID" = AAL."ArticleAttributeID"
    AND DECODE( AA."DeletionTimestamp", AAL."DeletionTimestamp", 1, 0 ) = 1
  INNER JOIN
  (
    SELECT DISTINCT XAA."ArticleRevisionID" AS "ArticleID", XAAL."Name" AS "Name", MAX(XAA."ID") AS "MaxArticleAttributeID"
    FROM "ArticleAttributeLang" XAAL
      INNER JOIN "ArticleAttribute" XAA
        ON XAA."ID" = XAAL."ArticleAttributeID" 
        AND DECODE( XAA."DeletionTimestamp", XAAL."DeletionTimestamp", 1, 0 ) = 1
        AND XAAL."LanguageID" = 9 -- TODO: change to the repository key language (9 = en_US, 7 = de_DE)
    WHERE XAAL."LanguageID" = 9 -- TODO: change to the repository key language (9 = en_US, 7 = de_DE)
    GROUP BY XAA."ArticleRevisionID", XAAL."Name"
    HAVING COUNT(*) > 1
  ) x
    ON x."ArticleID" = AA."ArticleRevisionID" 
    AND x."Name" = AAL."Name" 
    AND x."MaxArticleAttributeID" <> AA.ID
  WHERE AAL."LanguageID" = 9 -- TODO: change to the repository key language (9 = en_US, 7 = de_DE)
ORDER BY AR."CatalogID", AR."Identifier", AAL."Name"
 

MSSQL - correct duplicate attribute names

-- correct all duplicate attributes in the key language
DECLARE @KeyLanguageID BIGINT
SET @KeyLanguageID = 9 -- TODO: change to the repository key language ID (9 = en_US, 7 = de_DE)
DECLARE REPLACE_CURSOR CURSOR
FOR
  SELECT AAL.ID, AA.ArticleRevisionID
  FROM [ArticleAttribute] AA
    INNER JOIN [ArticleAttributeLang] AAL
      ON AA.[ID] = AAL.[ArticleAttributeID]
      AND AA.[DeletionTimestamp] = AAL.[DeletionTimestamp]
    INNER JOIN
    (
      SELECT DISTINCT XAA.[ArticleRevisionID] AS [ArticleID], XAAL.[Name] AS [Name], MAX(XAA.[ID]) AS [MaxArticleAttributeID]
      FROM [ArticleAttributeLang] XAAL
        INNER JOIN [ArticleAttribute] XAA
          ON XAA.[ID] = XAAL.[ArticleAttributeID]
          AND XAA.[DeletionTimestamp] = XAAL.[DeletionTimestamp]
          AND XAAL.[LanguageID] = @KeyLanguageID
      WHERE XAAL.[LanguageID] = @KeyLanguageID
      GROUP BY XAA.[ArticleRevisionID], XAAL.[Name]
      HAVING COUNT(*) > 1
    ) x
    ON x.[ArticleID] = AA.[ArticleRevisionID] 
    AND x.[Name] = AAL.[Name] 
    AND x.[MaxArticleAttributeID] <> AA.ID
  WHERE AAL.[LanguageID] = @KeyLanguageID
  ORDER BY AA.ArticleRevisionID;
DECLARE @ID BIGINT, @ArticleRevisionID BIGINT, @LastArticleRevisionID BIGINT, @Counter INT, @NameAddon NVARCHAR(5), @SQLSCRIPT NVARCHAR(1000);
SET @LastArticleRevisionID = -1
OPEN REPLACE_CURSOR;
FETCH NEXT FROM REPLACE_CURSOR INTO @ID, @ArticleRevisionID
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
    IF @LastArticleRevisionID <> @ArticleRevisionID
      BEGIN
        SET @LastArticleRevisionID = @ArticleRevisionID
        SET @Counter = 1
      END;
    SET @Counter = @Counter + 1
    SET @NameAddon = ' (' + CAST(@Counter AS NVARCHAR(3)) + ')'
    SET @SQLSCRIPT = 'UPDATE dbo.[ArticleAttributeLang] SET [Name] = (SUBSTRING([Name], 1, 244) + ''' + @NameAddon + ''') WHERE [ID] = ' + CAST(@ID AS NVARCHAR(25))
	PRINT @SQLSCRIPT
	EXEC sys.sp_executesql @SQLSCRIPT
    FETCH NEXT FROM REPLACE_CURSOR INTO @ID, @ArticleRevisionID
  END;
CLOSE REPLACE_CURSOR;
DEALLOCATE REPLACE_CURSOR;
GO

Oracle - correct duplicate attribute names

-- correct all duplicate attributes in the key language
SET SERVEROUTPUT ON
DECLARE
  KeyLanguageID         NUMBER := 9; -- TODO: change to the repository key language (9 = en_US, 7 = de_DE)
  ZID                   NUMBER;
  ArticleRevisionID     NUMBER;
  LastArticleRevisionID NUMBER := -1;
  Counter               NUMBER;
  NameAddon             NVARCHAR2(5);
  SQLSCRIPT             VARCHAR2(1000);
  
  CURSOR C1 (vKeyLanguageID IN NUMBER) IS SELECT AAL."ID", AA."ArticleRevisionID"
                                          FROM "ArticleAttribute" AA
                                            INNER JOIN "ArticleAttributeLang" AAL
                                              ON AA."ID" = AAL."ArticleAttributeID"
                                              AND DECODE( AA."DeletionTimestamp", AAL."DeletionTimestamp", 1, 0 ) = 1
                                            INNER JOIN
                                            (
                                              SELECT DISTINCT XAA."ArticleRevisionID" AS "ArticleID", XAAL."Name" AS "Name", MAX(XAA."ID") AS "MaxArticleAttributeID"
                                              FROM "ArticleAttributeLang" XAAL
                                                INNER JOIN "ArticleAttribute" XAA
                                                  ON XAA."ID" = XAAL."ArticleAttributeID"
                                                  AND DECODE( XAA."DeletionTimestamp", XAAL."DeletionTimestamp", 1, 0 ) = 1
                                                  AND XAAL."LanguageID" = vKeyLanguageID
                                              WHERE XAAL."LanguageID" = vKeyLanguageID
                                              GROUP BY XAA."ArticleRevisionID", XAAL."Name"
                                              HAVING COUNT(*) > 1
                                            ) x
                                              ON x."ArticleID" = AA."ArticleRevisionID" 
                                              AND x."Name" = AAL."Name" 
                                              AND x."MaxArticleAttributeID" <> AA.ID
                                          WHERE AAL."LanguageID" = vKeyLanguageID
                                          ORDER BY AA."ArticleRevisionID";
BEGIN
  OPEN C1(KeyLanguageID);
  LOOP
    FETCH C1 INTO ZID, ArticleRevisionID;
    EXIT WHEN C1%NOTFOUND;
    
    BEGIN
      IF (LastArticleRevisionID <> ArticleRevisionID) THEN
        BEGIN
          LastArticleRevisionID := ArticleRevisionID;
          Counter := 1;
        END;
      END IF;
      
      Counter := Counter + 1;
      NameAddon := ' (' || Counter || ')';
      
      SQLSCRIPT := 'UPDATE "ArticleAttributeLang" SET "Name" = (SUBSTR("Name", 1, 244) || ''' || NameAddon || ''') WHERE "ID" = ' || ZID;
      DBMS_OUTPUT.PUT_LINE(SQLSCRIPT);
      EXECUTE IMMEDIATE SQLSCRIPT;
    END;
  END LOOP;
  CLOSE C1;
END;
/