[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Select Query question

Posted on 2004-10-05
4
Medium Priority
?
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 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