Unsolved Query - 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

Unsolved Query

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-13 : 13:41:02

I have a table claim with fields(claimid,crn,mode)

Supposing i have data in the form

claimid CRN Mode
1 2 a
1 3 a
2 1 n

I have another table log with fields and data as follows:

claimid note
1
2

I have to update the log table's not based on the action mode of the table claim

for action mode a note should be a
for action mode n note should be n

this should be for a particular claim id's action mode this update needs to be done.

But my query is checking the action modes for all the claim id's and updating the notes for all the claim id's

How to track based on the claim ID's action mode and update only those claim ids

susan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-13 : 14:03:27

Could you post the query that you tried?

Jim

Everyday I learn something that somebody else already knew

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-13 : 14:06:46

update log
set log.notes='a'
from
claim c,
log l
where
not exists(select c.crn from claim c,log l
where c.claimid= l.claimid
and c.mode not in 'c')

this is updating for all the claim id's .IT is chekcing the mode for all the claim id 's

susan

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2011-04-13 : 14:06:46

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-13 : 14:41:54

For your sample data, the following will work. But...

UPDATE l
SET NOTES = 'A'
FROM CLAIM c
  INNER JOIN [LOG] l ON l.claimid = c.claimId

BUT...
1. If I change the sample data as shown below, it may or may not work as you expect. What do you want to happen in this case?

claimid CRN Mode
1 2 a
1 3 n
2 1 n

2. I see that you have a condition that reads "c.mode NOT IN 'c'". What does that do? I don't see a mode = c in the sample data.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-14 : 01:42:51

drop table #Claim
Create table #Claim (claimid int,crn int,mode varchar(2))
insert into #Claim
select 1,2,'a' union all
select 1,3,'a' union all
select 2,1,'n'

drop table #log
Create table #Log (claimid int ,notes nvarchar(2) null)
insert into #Log
select 1,'' union all
select 2,''
select * from #log
select * from #CLaim

update #Log
set #Log.notes='a'
from
#Claim c,
#Log L
where
exists
(
select c.crn from #Claim c,#Log L
where c.claimid= L.claimid
and c.mode <> 'c'
)

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources