Search code examples
sql-servermatchingidentifier

SQL Server - Granting same IDs to "matching" Variables IDs


this title may sounds a bit confusing at the beginning but I hope my example clarifies my intentions.

ID1      ID2   uniqueidentifier

A14      A21
A14      A55
A21      A14
A21      A55
A55      A14
A55      A21
...
A123     A22
A22      A123
...

Behind the IDs there a company names that I matched based on some criteria. Basically the displayed example means, that ID 'A14' matched with 'A21', but also the other way around (of course). And that there is a third company name that matched the criteria.

I can´t group them by company name, as the names are sometimes written differently as followed:
- The Example Company Corp.
- Example Company Corp.

Basically the first 6 rows would have the same identifier. And the other two as well. The uniqueidentifier will be a new one at this point, as I want to update this table with NEWID().

The question is: How I can assign uniqueidentifiers (NEWID() ) so that it looks like the output

Any suggestions for my problem?

I would be very happy for any kind of help, as this is a problem I´ve been thinking about for a long time.

EDIT: The expected output looks like this:

ID1      ID2   uniqueidentifier

A14      A21   XXER-WQEE-...
A14      A55   XXER-WQEE-...
A21      A14   XXER-WQEE-...
A21      A55   XXER-WQEE-...
A55      A14   XXER-WQEE-...
A55      A21   XXER-WQEE-...
...
A123     A22   IOKK-Q23A-...
A22      A123  IOKK-Q23A-...
...

Thanks MG


Solution

  • I think this one should give the result you want. Sorry, it's a bit messy. Ask questions if you need an explanation.

    DECLARE @Table1 TABLE (ID1 varchar(100), ID2 varchar(100), uniqueid uniqueidentifier)
    
    INSERT @Table1
        ( ID1, ID2, uniqueid )
    VALUES
        ( '1',  '2',  NULL  ),
        ( '2',  '1',  NULL  ),
        ( '2',  '3',  NULL  ),
        ( '3',  '2',  NULL  ),
        ( '1',  '3',  NULL  ),
        ( '3',  '1',  NULL  ),
        ( '4',  '5',  NULL  ),
        ( '5',  '4',  NULL  )
    
    DECLARE @tmp VARCHAR(100)
    DECLARE @NewID UNIQUEIDENTIFIER
    
    DECLARE @ID2 TABLE (ID2 varchar(100))
    
    WHILE EXISTS (SELECT 1 FROM @Table1 WHERE uniqueid IS NULL)
    BEGIN
    
    SET @NewID = NEWID()
    
    SELECT @tmp = ID1
    FROM @Table1
    WHERE uniqueid IS NULL
    
    DELETE @ID2
    
    UPDATE @Table1 
    SET uniqueid = @NewID
    OUTPUT Inserted.ID2
    INTO @ID2
    WHERE ID1 = @tmp
    
    WHILE EXISTS (SELECT 1 FROM @ID2 JOIN @Table1 ON [@Table1].ID1 = [@ID2].ID2 WHERE [@Table1].uniqueid IS NULL)
    BEGIN
    
        UPDATE t1 
        SET uniqueid = @NewID
        OUTPUT Inserted.ID2
        INTO @ID2
        FROM @Table1 t1
        JOIN @ID2 id2 ON t1.ID1 = id2.ID2
    
    END
    
    
    END
    
    SELECT * FROM @Table1