SQL Server Update Join Two Tables

Question

I'm having a bit of trouble getting MSSQL to cooperate with my intentions.

Examine the following...

Duplicates

UseId |   Use
-------------
2254  | Primary
2255  | Primary
2256  | Primary
2257  | Intersection
2258  | Monitor
2259  | Primary
2260  | Intersection
2261  | Primary
2264  | Monitor
2269  | Secondary

Uniques

UseID |   Use
--------------
2252  | Intersection
2253  | Monitor
2251  | Primary
2262  | Secondary

Many to Many Table

ID   | FKey | UseId
-------------------
1    |  1   | 2251
2    |  1   | 2252
3    |  2   | 2253
4    |  2   | 2262
5    |  3   | 2254
6    |  3   | 2257
7    |  3   | 2258
8    |  4   | 2255
9    |  4   | 2260
10   |  5   | 2259
11   |  5   | 2269

I want to run an update query on the "Many to Many" table that will match the use column on the duplicates and uniques, and then replace and "Duplicate" UseIds in the many to many table with their matching unique UseId...

The resulting good data would look like this....

Good Many to Many Table...

ID   | FKey | UseId
-------------------
1    |  1   | 2251
2    |  1   | 2252
3    |  2   | 2253
4    |  2   | 2262
5    |  3   | 2251
6    |  3   | 2252
7    |  3   | 2253
8    |  4   | 2251
9    |  4   | 2262
10   |  5   | 2251
11   |  5   | 2262

Any help is appreciated

In response to one of the posted answers... I'm having some difficulties. It doesn't seem to be working.

The update command is changing records 1-4 in the Many to Many table (even though they are good)... and not changing any of the other records.

This script provides an example of the problem.

CREATE TABLE Uniques
(
    UseID INT,
    [Use] VARCHAR(50)
)

INSERT INTO Uniques (UseID, [Use]) VALUES (2254, 'Primary')
INSERT INTO Uniques (UseID, [Use]) VALUES (2255, 'Primary')
INSERT INTO Uniques (UseID, [Use]) VALUES (2256, 'Primary')
INSERT INTO Uniques (UseID, [Use]) VALUES (2257, 'Intersection')
INSERT INTO Uniques (UseID, [Use]) VALUES (2258, 'Monitor')
INSERT INTO Uniques (UseID, [Use]) VALUES (2259, 'Primary')
INSERT INTO Uniques (UseID, [Use]) VALUES (2260, 'Intersection')
INSERT INTO Uniques (UseID, [Use]) VALUES (2261, 'Primary')
INSERT INTO Uniques (UseID, [Use]) VALUES (2264, 'Monitor')
INSERT INTO Uniques (UseID, [Use]) VALUES (2269, 'Secondary')

CREATE TABLE Duplicates
(
    UseID INT,
    [Use] VARCHAR(50)
)

INSERT INTO Duplicates (UseID, [Use]) VALUES (2252, 'Intersection')
INSERT INTO Duplicates (UseID, [Use]) VALUES (2253, 'Monitor')
INSERT INTO Duplicates (UseID, [Use]) VALUES (2251, 'Primary')
INSERT INTO Duplicates (UseID, [Use]) VALUES (2262, 'Secondary')

CREATE TABLE ManyToMany
(
    Id INT,
    FKey INT,
    UseId INT
)

INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (1, 1, 2251)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (2, 1, 2252)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (3, 2, 2253)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (4, 2, 2262)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (5, 3, 2254)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (6, 3, 2257)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (7, 3, 2258)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (8, 4, 2255)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (9, 4, 2260)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (10, 5, 2259)
INSERT INTO ManyToMany (Id, FKey, UseId) VALUES (11, 5, 2269)

SELECT * FROM Uniques
SELECT * FROM Duplicates
SELECT * FROM ManyToMany

UPDATE  m
SET     m.UseId = u.UseId
FROM    ManyToMany m
        JOIN Duplicates d ON m.UseId = d.UseId
        JOIN Uniques u ON d.[Use] = u.[Use];

SELECT * FROM ManyToMany

DROP TABLE Uniques
DROP TABLE Duplicates
DROP TABLE ManyToMany

this is confusing because the data is not complete. Where are you getting the ID and FKey from? How are you creating your 'Many to Many' table right now? — Jenn
– Jenn, Commented Jul 2, 2014 at 20:38
One wonders how you got into this mess in the first place, but Kevin Suchlicki's answer looks correct. — Tony Hopkinson
– Tony Hopkinson, Commented Jul 2, 2014 at 20:42
In the many to many table, ID is a an INT IDENTITY field. The foreign key just points at another table that is irrelevant (probably should have left it out of the example). — imdandman
– imdandman, Commented Jul 2, 2014 at 20:49
For inquiring minds, my task is to read in an XML file, and parse it into some tables. The result is a lot duplicate data because of the design of the XML file. I need to delete all the duplicate records from one table, but it has a relationship to the many to many table... so the "unique" and "duplicate" datasets above are the result of queries in the main table... So I need to update all the records in the "many to many" table with their "unique" counterparts before I delete the original duplicate records.... if that makes sense... — imdandman
– imdandman, Commented Jul 2, 2014 at 20:53
Kevin's answer is correct, but you mis-named your tables. Change the table Uniques to Duplicates and Duplicates to Uniques and his answer will yield the correct results. — mdisibio
– mdisibio, Commented Jul 2, 2014 at 23:15

Kevin Suchlicki · Accepted Answer · 2014-07-02 20:39:49Z

3

UPDATE  m
SET     m.UseId = u.UseId
FROM    ManyToMany m
        JOIN Duplicates d ON m.UseId = d.UseId
        JOIN Uniques u ON d.[Use] = u.[Use];

answered Jul 2, 2014 at 20:39

Kevin Suchlicki

3,1542 gold badges17 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mdisibio Over a year ago

This answer is correct, but you (imdandman) mis-named your tables in your posted sample. Change the table Uniques to Duplicates and Duplicates to Uniques and this answer will yield the correct results.

Kevin Suchlicki Over a year ago

@mdisibio Thanks for looking at this

imdandman Over a year ago

Thank you for the help! Sorry for my confusion.

Collectives™ on Stack Overflow

SQL Server Update Join Two Tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related