Solved
Select Query question
Posted on 2004-10-05
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.