Update Statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Statment

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-06-11 : 12:53:58

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 12:57:13

You used t1 as the alias for both. Just need to update the second one to t2, though I'm confused if this query is what you want:
UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t2 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-06-11 : 13:03:35

What I actually wanted to do is use by query to get on my counts, but the one that I am having problems with, the count is off because it is being grouped.

This is what I originally had.

Select Distinct
[MSA Group],
[Spec 1],
count (distinct [PCS Number] ) as [Count Total],
count (case when prop_disc <> '0.00' then 1 else null end) as [COUNT on Billed Charges],
avg(nullif(prop_disc, 0)) as [Average Discount on Billed Charges]

from MonthEnds.dbo.vw_All_Products
Where AHS = 'X' and
product = 'DGH' and
st IN('PA','NJ','DE','MD') and
[MSA Group] is not null and
[Spec 1] = 'ACUTE CARE HOSPITAL' and
[MSA Group] = 'Allentown-Bethlehem-Easton'
group by [MSA Group], [Spec 1]
order by [Spec 1]---[MSA Group]

My count is off on the field [Count on Billed Charges] because of the grouping. Is it possible to do this count a different way without the grouping?

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-06-11 : 13:09:40

I just found another way. Thanks for your help.

SELECT

[MSA Group],
[Spec 1],
count (distinct [PCS Number] ) as [Count Total],
avg(nullif(prop_disc, 0)) as [Average Discount on Billed Charges],

(

SELECT Count(Distinct [PCS Number])

FROM MonthEnds.dbo.vw_All_Products

WHERE AHS = 'X' and
product = 'DGH' and
st IN('PA','NJ','DE','MD') and
[MSA Group] is not null and
[Spec 1] = 'ACUTE CARE HOSPITAL' and
[MSA Group] = 'Allentown-Bethlehem-Easton' and prop_disc <> '0.00'

) AS TotalQuantity

from MonthEnds.dbo.vw_All_Products
Where AHS = 'X' and
product = 'DGH' and
st IN('PA','NJ','DE','MD') and
[MSA Group] is not null and
[Spec 1] = 'ACUTE CARE HOSPITAL' and
[MSA Group] = 'Allentown-Bethlehem-Easton'
group by [MSA Group], [Spec 1]
order by [Spec 1];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources