Select Statement SubQuery in the From Clause

Question

Select Statement SubQuery in the From Clause

Scubba-Steve

Ten Centuries

Points: 1373
More actions
August 1, 2016 at 7:01 am

#312148

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Scubba-Steve Ten Centuries Points: 1373 More actions · Answer 3

Wow. I didnt catch that. Thanks for the help. Works like a charm.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Scubba-Steve Ten Centuries Points: 1373 More actions · Answer 5

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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