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.

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.

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

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

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

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.

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