Help with an Update Statment

[thetaamommy]

CREATE Table CO(CustID int, OfficerSID varchar(10), OfficerRank
tinyint)

Insert Into CO
VALUES (1, 'KURT', 1)

Insert Into CO
VALUES (1, 'ALEX', 2)

Insert Into CO
VALUES (1, 'COLIN', 3)

Insert Into CO
VALUES (1, 'RUSTY', 4)

Insert Into CO

VALUES (2, 'CAL', 2)

Insert Into CO
VALUES (2, 'VIN', 4)
Insert Into CO
VALUES (3, 'FIDA', 2)

Insert Into CO
VALUES (3, 'ZOTO', 1)

Insert Into CO
VALUES (4, 'ZARA', 3)

Insert Into CO
VALUES (4, 'MAIR', 2)
Insert Into CO
VALUES (5, 'tara', 4)

Insert Into CO
VALUES (5, 'sara', 4)
I have an output table which is primarily like a temp table and then i
run my reports just grabbing data from this table.
This table looks like

CREATE Table tbl_Output (CustID int, OfficerSID1 varchar(10),
OfficerSID2 varchar(10))

I need this table to be populated thus
The offierSID1 needs to filled with officer who are ranked highest, be
it 1,2,3 or 4
Then comes OfficerSID2 which need to be filled with secondhighest
ranking, be it 1, 2, 3, 4
There can be two officersid with same ranking so then both of them
would appear in hte output table

The output table is already populated with CustID. so i need update
statements to fill in the officersids

So the output needs to look like
1, Kurt, Alex
2, Cal, Vin
3, Zoto, Fida
4, Mair, Zara
5, Tara, Sara or 5, Sara, Tara

[thetaamommy]

Plamen Thank you so much and to all, I apologize. I work on SQL 2000
and need a solution for that. Thank you

[thetaamommy]

Thanks a bunch Plamen : It worked like a charm

[Tony Rogerson]

The next question is why do you want to build a table that has to be

constantly updated when you can use a VIEW that is always right?

This is fundemental design - beginners 101.

Summary tables are used to assist performance and scalability.

It could often take 20 seconds - 5 hours to work out the aggregations
depending on the data set and the complexity of the calculations.

In OLTP data is read many many more times than it is written.

Now imagine your query method took 10 seconds to run for a single user; 100
users running the same query would flat line your CPU and probably push the
query time out into minutes - not very scalable.

Unfortunetly because you are classroom based you don't see these real world
problems; you are best getting out and doing a beginners programming job for
a couple of years to get the necessary fundementals.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

[Ed Murphy]

Tony Rogerson wrote:

>The next question is why do you want to build a table that has to be
constantly updated when you can use a VIEW that is always right?

This is fundemental design - beginners 101.

Summary tables are used to assist performance and scalability.

It could often take 20 seconds - 5 hours to work out the aggregations
depending on the data set and the complexity of the calculations.

In OLTP data is read many many more times than it is written.

Now imagine your query method took 10 seconds to run for a single user; 100
users running the same query would flat line your CPU and probably push the
query time out into minutes - not very scalable.

Unfortunetly because you are classroom based you don't see these real world
problems; you are best getting out and doing a beginners programming job for
a couple of years to get the necessary fundementals.

I have to agree. (I say "have to" because I don't like agreeing with
people with a history of rudeness - though the above-quoted message is
less so than usual.) Anyway, recent real-world example:

* Inventory item numbers (e.g. 1234-BLUE) need to be parsed into
style, color, size (e.g. 1234, BLUE, n/a). Several dozen exceptions
are hardcoded into the logic.

* View took 2 seconds. Front end is web site, so this is too slow.

* Replaced view with a table that is refreshed from the view by a
SQL Server Agent job every 15 minutes. This level of near-real-time
is acceptable in context (short delay between new items being
approved and appearing on the site).

[--CELKO--]

>Anyway, recent real-world example:

* Inventory item numbers (e.g. 1234-BLUE) need to be parsed into
style, color, size (e.g. 1234, BLUE, n/a). Several dozen
exceptions
are hardcoded into the logic.

* View took 2 seconds. Front end is web site, so this is too slow.

* Replaced view with a table that is refreshed from the view by a
SQL Server Agent job every 15 minutes. This level of near-real-
time
is acceptable in context (short delay between new items being
approved and appearing on the site).
<<

Ouch! That much refreshing, redundancy and uncertainty for 15 minutes
at a time sounds bad to me. Following one of my heuristics, bad DDL
leads to worse DML, and eventually to procedural coding, sequential
processing, cursors and other kludges (at this point I tell the joke
about Levant the Tailor and the suit that doesn't fit).

Why not normalize the table to 1NF instead? The style, color and size
seem to be attributes that define a 3-column key you call inventory
number. But you crammed them into a non-scalar column, so you are not
in 1NF. If you need to display them as a single string, then you can
add a VIEW or computed column for that purpose. This would avoid
those dozen exceptions and procedural logic, but you might need a
"moose killer" CHECK() constraint on the key for data integrity.

[Tony Rogerson]

I have to agree. (I say "have to" because I don't like agreeing with

people with a history of rudeness - though the above-quoted message is
less so than usual.) Anyway, recent real-world example:

Ed - I'm rude purely to the likes of --celko-- because of his deeming and
dam right outlandishly bad attitude on this forum.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

[DA Morgan]

Tony Rogerson wrote:

>I have to agree. (I say "have to" because I don't like agreeing with
people with a history of rudeness - though the above-quoted message is
less so than usual.) Anyway, recent real-world example:

Ed - I'm rude purely to the likes of --celko-- because of his deeming
and dam right outlandishly bad attitude on this forum.

No Tony: You are uncivil, rude and lacking in social graces across
the board. Do not flatter yourself by thinking otherwise.

Every comment made about you in all of these groups, from people
from different countries and different cultures, all communicate
the same fundamental opinion ... you a almost always belligerent.

I doubt many, if any, of us have ever met you. The impression of you
we have is the one you have created. If you don't like that then the
onus is upon you to start with an apology.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu (replace x with u to respond)

[Erland Sommarskog]

DA Morgan (da******@psoug .org) writes:

No Tony: You are uncivil, rude and lacking in social graces across
the board. Do not flatter yourself by thinking otherwise.

Since I know Tony personally, I can assure you that he does not lack
social grace across the board. He might run a little run a little low
on it when he talks with people like you or Celko.

But he would never flame someone who have made his first stumbling
attempts to use SQL Server. In difference to you know who.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

No Tony: You are uncivil, rude and lacking in social graces across

the board. Do not flatter yourself by thinking otherwise.

Every comment made about you in all of these groups, from people
from different countries and different cultures, all communicate
the same fundamental opinion ... you a almost always belligerent.

I doubt many, if any, of us have ever met you. The impression of you
we have is the one you have created. If you don't like that then the
onus is upon you to start with an apology.

I actually thought twice about gracing your post with a response but I
thought I ought because you might learn something about yourself.

Lol, I can count on my right hand the number of people in the SQL Server
groups I'm ruded to - (1) celko; Oracle (3) - you, sybrand and hp; well -
anybody else who attacks me on there for posting truth, accurate and direct
posts.

Now, you on the other hand, well.... all too often people comment on your
uncivil, dictitorial attitude and biased opinions on the oracle groups; lots
of folk have sent me private emails congratulating me whenever I pick you up
on the misinformation and bad attitude you post around SQL Server.

I've been around for over a decade on the NNTP SQL Server groups and have
met most the other SQL Server MVP's and meet quite a lot of people in the UK
via SQLBits and the User Group I run; I know full well what people think of
me - don't you worry on that score.

As to an apology; well - to you, celko - no, I don't think that will be
forth coming; I treat people as they treat others.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]