August 1, 2016 at 7:01 am
Hey guys this is probably an easy one for you all here. I have been working on this Subquery in a Select statement for a couple of hours here and there and have totally restructured it to make it easier to read. I am trying to Update a table from itself to create a list of the unitId's that are similar for consolidating my Database Table called TN. Problem is that the On Clause keeps giving me syntax errors and I have exhausted all of my resources to locate the issue. Any ideas?
USE People
GO
SELECT mu.Id, mu.name, mu.Code, Count(mu.Id) AS UnitCount
Into TN FROM Ref.MilitaryUnit mu
LEFT JOIN Person.DMDC dmdc ON mu.Id=dmdc.Id
LEFT JOIN Person.Military mp ON mu.Id=mp.UnitId
Group By mu.Id, mu.Name, mu.Code
HAVING COUNT(mu.Id) > 1;
Alter Table TN Add IdToBe int NULL;
UPDATE TN
SET TN.Id=TN2.IdToBe
FROM (SELECT MIN(Id)AS IdToBe, Name
FROM TN
GROUP BY Name) AS TN2; ON TN.Name=TN2.Name AND TN.Code=TN2.Code
ORDER By UnitCount DESC
August 1, 2016 at 7:09 am
You don't have a Code column in your derived table (subquery). Either you add it to the query or remove it from the join clause.
August 1, 2016 at 8:21 am
SELECT mu.Id, mu.name, mu.Code,
Count(mu.Id) AS UnitCount
Into TN -- temporary table?
FROM Ref.MilitaryUnit mu
LEFT JOIN Person.DMDC dmdc ON mu.Id = dmdc.Id
LEFT JOIN Person.Military mp ON mu.Id = mp.UnitId
Group By mu.Id, mu.Name, mu.Code
HAVING COUNT(mu.Id) > 1;
--Alter Table TN Add IdToBe int NULL;
UPDATE TN
SET Id = TN2.IdToBe
FROM TN
INNER JOIN (
SELECT MIN(Id) AS IdToBe, Name, Code
FROM TN
GROUP BY Name, Code
) AS TN2 --; -- ##
ON TN.Name = TN2.Name AND TN.Code=TN2.Code
--ORDER By UnitCount DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2016 at 8:49 am
Wow. I didnt catch that. Thanks for the help. Works like a charm.
August 1, 2016 at 8:56 am
Scubba-Steve (8/1/2016)
Wow. I didnt catch that. Thanks for the help. Works like a charm.
Cool.
Are you really on SQL Server 7 or 2000?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2016 at 8:59 am
Actually I am on 2008. I just asked the question on the first SQL forum that I found. Did I post in the wrong place?
August 1, 2016 at 9:07 am
Scubba-Steve (8/1/2016)
Actually I am on 2008. I just asked the question on the first SQL forum that I found. Did I post in the wrong place?
Yes but it's no big deal. The reason I asked is because you have an extended set of toys in 2008. This might get you working in a slightly different direction:
;WITH ChosenGroups AS (
SELECT mu.Id, mu.name, mu.Code,
cnt1 = COUNT(mu.Id) OVER(PARTITION BY mu.Id, mu.Name, mu.Code),
IdToBe = MIN(mu.Id) OVER(PARTITION BY Name, Code)
FROM Ref.MilitaryUnit mu
LEFT JOIN Person.DMDC dmdc ON mu.Id = dmdc.Id
LEFT JOIN Person.Military mp ON mu.Id = mp.UnitId
)
SELECT *
FROM ChosenGroups
WHERE cnt1 > 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply