Hi All :
I have a TableA with a columns CustomerRecNo, A_ID, A_Name
There is a tableB with columns,CustomerRecNo, OfficerSID, OfficerName,
OfficerRank
Now The TableA is populated with a lot of CustomerRecNos.
I have to update the rows in TableA with corresponding officersids and
officernames from TableB for their respective cusotmerrecnos in TableA
The rules for assigning an officersid and officername to a
customerrecno is based on the rankings.
for example Customer X can have officers 001, 002, 003 and they are
given rankigns of 1, 2, 3
First we needs assign officers with ranking 1, if they are not found,
then assign to customerX, an officersid with ranking 2, if they are
not found then assign ranking 3.
so i have the following which works fine
Update TableA
Set A_ID = TableB.OfficerSID, A_Name = TableB.OfficerName
From TablA A
Inner Join TableB B
On A.CustomerRecNo = B.CustomerRecNo
And B.OfficerRank In (1,2,3)
Update TableA
Set A_ID = TableB.OfficerSID, A_Name = TableB.OfficerName
From TablA A
Inner Join TableB B
On A.CustomerRecNo = B.CustomerRecNo
And B.OfficerRank In (2,3)
Update TableA
Set A_ID = TableB.OfficerSID, A_Name = TableB.OfficerName
From TablA A
Inner Join TableB B
On A.CustomerRecNo = B.CustomerRecNo
And B.OfficerRank In (3)
Since I was doing this in order, i thought it will work,
I did do a while loop to simulate row by row processing, that is read
one customerrecno , input that to tableb, get corresponding officers
and update tableA, but i have 3 million recs in tablea and 5 million
in tableb , that it took some time.
so i
had to do set operation and i tried the above. It did seem to work for
some, but surely not working in certain other cases.That is the
rankings got mixed up. the officer with ranking 2 was updated onto
tableA when there was an officer with ranking 1
So if you can help me on how to do an update where i can use top and
order by for this .
i tried the following, but it was giving the same officersid to all 3
million customerrecnos
Update TableA
Set CSOID = (Select Top 1 B.OfficerSID
From TableB B
Inner Join TableA Ext
On B.CustomerRecNo = Ext.CustomerRecNo
Where B.OfficerRank In (1,2,3)
Order By B.OfficerRank)
From TableA A
Inner Join TableB B
On B.CustomerRecNo = A.CustomerRecNo
Thank you !!
RS
|
