Solved

Select Query question

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

777 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