?
Solved

Select Query question

Posted on 2004-10-05
4
Medium Priority
?
270 Views
Last Modified: 2011-08-18
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.
0
Comment
Question by:dbdoshi
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12225869
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
 
LVL 14

Expert Comment

by:Jan Franek
ID: 12225876
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
 
LVL 6

Accepted Solution

by:
OlegP earned 1000 total points
ID: 12225932
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12225937
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question