dbdoshi
asked on
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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