Update including Top, Orderby and updating 2 columns in an update

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

Re: Update including Top, Orderby and updating 2 columns in an update

>> I have a VagueCustomerStuff with a columns customer_rec_nbr, vague_id, officer_name There is a Tickets with columns, customer_rec_nbr, officer_id, officer_name,
officer_ranking. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules, so that people can read your posting
and understand it (I.e names like Table_A is pretty useless). Sample
data is also a good idea, along with clear specifications. It is very
hard to debug code when you do not let us see it. If you want to learn
how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html <<

Here is an attempt to clean up your narrative with DDL. I had to
Reade the narrative to figure out that you actually change the data
element names from table to table. That is a horrible programming
practice.

CREATE TABLE VagueCustomerStuff
(customer_rec_nbr INTEGER NOT NULL
REFERENCES CustomerRecords (customer_rec_nbr), -- this table and
Customers do exist, don't they?
officer_id INTEGER, -- should refrences Oficers table; null is
unassigned
officer_name CHAR(25) , -- should be in Officers table
PRIMARY KEY (??)); --no DDL to tell us!

Since you did not tell us anything, we assume that these are traffic
tickets? Hey, why not?

CREATE TABLE Tickets
(customer_rec_nbr INTEGER NOT NULL, --shoudl reference customer
records table
officer_id INTEGER NOT NULL, -- should reference Officers table
officer_name CHAR(25) NOT NULL, -- should be in Officers table
officer_rank CHAR(5) NOT NULL, -- should be in Officers table
PRIMARY KEY (??)); -- no DDL to tell us!

Now, what is the first thing you notice about this table? It is not
even in 3NF!! Officers data should be in an Officers table; otherwise
"officer_id" makes no sense. Customers are clearly entities, but you
show no table to model them.

>> I have to update the rows in VagueCustomerStuff with corresponding
officer_id's and officer_names from Tickets for their respective
customer_rec_nbr's in VagueCustomerStuff Table. <<

1) There is no such columns as officer_id and officer_name in the
first table according to your vague narrative. I had to guess at that
because of your changing data element names.
2) This sounds like redundant data -- why do you want the same data in
two places?

>> The rules for assigning an officer_id and officer_name to a customer_rec_nbr is based on the rankings. For example Customer X can have officers 001, 002, 003 and they are given rankings of 1, 2, 3 <<

Where is the officer_ranking column? An officer's rank is a VERY
different thing from his rank. Where do we find that Customer X is
qualified for (1, 2, 3) level help and Customer Y is qualified for (2,
3, 4) level help, etc.?

>> First we need to assign officers with ranking 1, if they are not found, then assign to customer X, an officer_id with ranking 2, if they are not found then assign ranking 3. <<

>> So I have the following which works fine <<

It is awful on several point, even ignoring the invalid schema
design.
1) Proprietary syntax which can be unpredictable
2) Multiple updates instead of single CASE expression or subquery to
find an officer with the right ranking.
3) Not even close to properly normalized

>> Since I was doing this in order, I thought it will work, I did do a WHILE loop to simulate row by row processing, <<

This just gets worse and worse. There is almost never any need for
RBAR (row by agonizing row) processing in SQL

>> ... that reads one customer_rec_nbr, input that to Tickets, get corresponding officers and update VagueCustomerStuff, but I have 3 million recs in VagueCustomerStuff and 5 million in Tickets, that it took some time. <<

Bad DDL leads to bad DML. "No matter how far you have gone down the
wrong road, turn around" -- Turkish proverb.

>> 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 VagueCustomerStuff 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 officer_id to all 3 million customer_rec_nbrs <<

So you already decided on another proprietary approach to kludge more
bad data? And where did that magical "CSOID" come from? You never
mentioned it before.

You need
1) A table to model Customers
2) A table to model customer records, which references Customers
3) A table to model officers (which probably will have their rankings)
4) A table to model the relationship (tickets?
CustomerOfficerAssignments?) between a customer and an officer, with
references to both.

we also need more business rules for natching these together.

Your UPDATE would probably look something like this:

UPDATE Tickets
SET officer_id
= (SELECT O1.officer_id
FROM Officers AS O1
WHERE O1.officer_ranking
= (SELECT MIN (O2.Officer_ranking)
FROM Officers AS O2
WHERE O2.Officer_ranking IN (1, 2, 3)
AND << more rules >>))
WHERE officer_id IS NULL;

Re: Update including Top, Orderby and updating 2 columns in an update query using TOP

(rshivaraman@gmail.com) writes:
> 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)

If I understand your narrative correctly, there can be more than
onw row in TableB for a CustomerRecNo in TableA. In such case, it
is not deterministic which result you get.

Here is a solution that may work on SQL 2005:

UPDATE TableA
SET A_ID = b.OfficerSID
FROM TableA a
JOIN (SELECT CustomerRecNo, OfficerSID,
rowno = row_number() OVER (PARTITION BY CustomerRecNo
ORDER BY OfficerRank)
FROM TableB) ON a.CustomerRecNo = b.CustomerRecNo

I will have admit it is a bit of a guesswork, as I am not sure that
I know your keys.

If the query above does not meet your requirements, I suggest that
you post:

o CREATE TABLE statements for tables (preferrably simplified),
including keys.
o INSERT statements with sample data.
o The desired result given the sample.
o The version of SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx