Select Query question

Hi, I have a table (A) like:

MID       X1   X2   X3   X4
abc       2     5     1     23
xyz       7     13    4      0

Table (B) goes like this:

MID         PID
abc          p
abc          q
xyz          m
xyz          n
xyz          s
xyz          t

Thus, there is primary key-foreign key relationship between A and B for column MID. Also, PID column is unique in table B.

Now, I have another table (C):

PID    Level
p         1
q         4
m        2
n         3
s         4
t          3

Here, PID is again unique. Also, level can be only 1 or 2 or 3 or 4.

Now, I want to write an update statement to update table A for columns X1, X2, X3 and X4. The logic is, for each MID in table A, I want to match with PID is in table B and find the count of 1,2,3 or 4 for that group of MID. So for MID xyz, there are 4 PIDs (m,n,s,t). So after I run the update statement, X1 should be 0, X2 should be 1, X3 should be 2 and X4 should be 1 for MID xyz.

I don't want to use cursors as I already know how to tackle it with cursors. Thanks a bunch.
dbdoshiAsked:
Who is Participating?
 
OlegPConnect With a Mentor Commented:
changed Jan_Franek's code

update A
set X1 = B.X1,
X2 = B.X2,
X3 = B.X3,
X4 = B.X4
from
A INNER JOIN
(
SELECT
A.MID,
X1 = sum( case when C.Level = 1 then 1 else 0 end ),
X2 = sum( case when C.Level = 2 then 1 else 0 end ),
X3 = sum( case when C.Level = 3 then 1 else 0 end ),
X4 = sum( case when C.Level = 4 then 1 else 0 end )
FROM
A, B, C
where A.MID = B.MID and B.PID = C.PID
group by A.MID
) B
ON A.MID=B.MID
0
 
Jan FranekCommented:
Try:

update A
set X1 = sum( case when C.Level = 1 then 1 else 0 end ),
X2 = sum( case when C.Level = 2 then 1 else 0 end )
X3 = sum( case when C.Level = 3 then 1 else 0 end )
X4 = sum( case when C.Level = 4 then 1 else 0 end )
from A, B, C
where A.MID = B.MID
and B.PID = C.PID
group by A.MID
0
 
Jan FranekCommented:
Sorry, I missed some commas:

update A
set X1 = sum( case when C.Level = 1 then 1 else 0 end ),
X2 = sum( case when C.Level = 2 then 1 else 0 end ),
X3 = sum( case when C.Level = 3 then 1 else 0 end ),
X4 = sum( case when C.Level = 4 then 1 else 0 end )
from A, B, C
where A.MID = B.MID
and B.PID = C.PID
group by A.MID
0
 
HilaireCommented:
Can't use group by in an update statement AFAIK
but you can gruop in a subquery as follows

update a
set X1 = isnull(d.X1, 0), X2 = isnull(d.X2, 0), X3 = isnull(d.X3, 0), X4 = isnull(d.X4, 0)
from tableA a
left outer join (
   select b.mid,
   sum(case when c.level = 1 then 1 else 0 end) as X1,
   sum(case when c.level = 2 then 1 else 0 end) as X2,
   sum(case when c.level = 3 then 1 else 0 end) as X3,
   sum(case when c.level = 4 then 1 else 0 end) as X4
   from tableB b inner join tableC c on b.Pid = C.Pid
   group by b.Mid
) d on a.Mid = d.Mid

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.