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