Help with update statement

Experts,

I want to update my status field to 4 on the table called credit_master where the below query gives a result.

detail and master are linked by column cid

select 
  m.cid,
  count(distinct d.status) stat
from credit_master m, 
         credit_detail d 
where m.cid = d.cid
group by m.cid
having count(distinct d.status) =2

Open in new window

FutureDBA-Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
I think this is what you're trying to say

Given a set of cid values returned by above query,  Update all records with those cids to status 4


UPDATE credit_master
   SET status = 4
 WHERE cid IN (SELECT m.cid
                 FROM credit_master m, credit_detail d
                WHERE m.cid = d.cid
               GROUP BY m.cid
               HAVING COUNT(DISTINCT d.status) = 2)
0
 
sdstuberCommented:
>>> where the below query gives a result.

what does that mean?
0
 
FutureDBA-Author Commented:
This query

select 
  m.cid,
  count(distinct d.status) stat
from credit_master m, 
         credit_detail d 
where m.cid = d.cid
group by m.cid
having count(distinct d.status) =2

Open in new window


Gives me these results
m.cid	status
--------------
172	2
135	2
137	2
149	2
161	2

Open in new window


I want to update m.status to 4 where m.cid = status results havind a 2 on  d.status
0
 
FutureDBA-Author Commented:
Does this seem right?
update credit_master m set status = 5
where exists 
(select 
  count(distinct d.status) stat
from credit_detail d 
where m.cid = d.cid
group by m.cid
having count(distinct d.status) > 1)

Open in new window

0
 
FutureDBA-Author Commented:
Correct.

Thanks,

I think I was on the right path.
0
All Courses

From novice to tech pro — start learning today.