Solved

Select Query question

Posted on 2004-10-05
4
265 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 250 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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