Update Statement Question - 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 Statement Question

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2015-02-05 : 14:20:32

I have this update statement I am trying to use, to update a table. My problem is if there is no data in the select statement, it still adds number to the columns. I am thinking my join is off.

How can I have this update statement work to put blank value in if there are no counts?

UPDATE T_AXA_BreakDown_Claims
SET [Claim Count Conm] = t2.[Claim Count Conm]
FROM T_AXA_BreakDown_Claims t1
INNER JOIN
(select
clm_rcvd,
case
when CLM_form = 'H' then 'HCFA'
When clm_form = 'U' then 'UB'
else clm_form
end as clm_form,
count (clm_id1) as [Claim Count Con]
from impact.dbo.clm with (nolock)
where
clm_cc1 = 13490
and clm_rcvd = '2015-02-24 00:00:00.000' --- > GETDATE()-1 ----BETWEEN getdate() - 7 AND getdate() ----> GETDATE()-1
and clm_61a = 'Consilium'
and clm_clir <> 7974
group by clm_rcvd,clm_form) t2 on t1.[Todays Date] = t2.clm_rcvd and t1.clm_form = t2.clm_form

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-05 : 14:26:35

Please explain with sample data this: "My problem is if there is no data in the select statement, it still adds number to the columns."

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

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-05 : 14:48:44

Execute the command above (i just replace the update by a select) and see if the result set is the result set you want update. If not you need to adjust your "inner join"

SELECT
t2.[Claim Count Conm], -- the new value
T1.*
FROM T_AXA_BreakDown_Claims t1
INNER JOIN
(select
clm_rcvd,
case
when CLM_form = 'H' then 'HCFA'
When clm_form = 'U' then 'UB'
else clm_form
end as clm_form,
count (clm_id1) as [Claim Count Con]
from impact.dbo.clm with (nolock)
where
clm_cc1 = 13490
and clm_rcvd = '2015-02-24 00:00:00.000' --- > GETDATE()-1 ----BETWEEN getdate() - 7 AND getdate() ----> GETDATE()-1
and clm_61a = 'Consilium'
and clm_clir <> 7974
group by clm_rcvd,clm_form) t2 on t1.[Todays Date] = t2.clm_rcvd and t1.clm_form = t2.clm_form

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources