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.