Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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

0
FutureDBA-
Asked:
FutureDBA-
  • 3
  • 2
1 Solution
 
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
 
sdstuberCommented:
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
 
FutureDBA-Author Commented:
Correct.

Thanks,

I think I was on the right path.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now