Hi,

Here is my sample table.

create table test
(journal_ctrl_num  nvarchar(16) not null,
sequence_id       int          not null,
document_2        nvarchar(16) not null)

alter table test
add constraint PK_test primary key (journal_ctrl_num, sequence_id)

Here is sample data in the table.

insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',3,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',4,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',5,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',6,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',7,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',52,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',71,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000002',77,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',3,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',4,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',5,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',6,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',7,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',52,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',71,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000008',77,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',3,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',4,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',5,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',6,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',7,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',52,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',71,'VCH0000006')
insert into test (journal_ctrl_num, sequence_id, document_2) values
('JNL0000000063',77,'VCH0000006')

Now, I want to alter the table to add a new column called document_2_seq_id. 

alter table test
add document_2_seq_id int null

I want to populate this new column with sequential numbers for each grouping
of journal_ctrl_num, sequence_id, and document_2.   Here is the sample result
that I want.

journal_ctrl_num sequence_id document_2       document_2_seq_id
---------------- ----------- ---------------- -----------------
JNL0000000002    3           VCH0000006       1
JNL0000000002    4           VCH0000006       2
JNL0000000002    5           VCH0000006       3
JNL0000000002    6           VCH0000006       4
JNL0000000002    7           VCH0000006       5
JNL0000000002    52          VCH0000006       6
JNL0000000002    71          VCH0000006       7
JNL0000000002    77          VCH0000006       8

JNL0000000008    3           VCH0000006       1
JNL0000000008    4           VCH0000006       2
JNL0000000008    5           VCH0000006       3
JNL0000000008    6           VCH0000006       4
JNL0000000008    7           VCH0000006       5
JNL0000000008    52          VCH0000006       6
JNL0000000008    71          VCH0000006       7
JNL0000000008    77          VCH0000006       8

JNL0000000063    3           VCH0000006       1
JNL0000000063    4           VCH0000006       2
JNL0000000063    5           VCH0000006       3
JNL0000000063    6           VCH0000006       4
JNL0000000063    7           VCH0000006       5
JNL0000000063    52          VCH0000006       6
JNL0000000063    71          VCH0000006       7
JNL0000000063    77          VCH0000006       8

How can I properly write the update statement to do this type of an update
to the table?  Currently, the only way I'm thinking of doing is by writing a
cursor, which I now is a big no-no due to performance reasons. 

Any assistance is greatly appreciated!

Thanks in advance,
Dee

On Wed, 14 Nov 2007 10:24:01 -0800, bpdee
<bp***@discussions.microsoft.com> wrote:

What version of SQL Server are you running?  The answer for SQL Server
2005 is much simpler than that for SQL Server 2000.

Roy Harvey
Beacon Falls, CT

Hi Roy!

I'm currently running SQL Server 2005.

Thanks!
Dee

Show quoteHide quote

Try this:

  UPDATE test
  SET document_2_seq_id = (
    SELECT COUNT(*)
    FROM Test T2
    WHERE T2.journal_ctrl_num = Test.journal_ctrl_num
    AND   T2.sequence_id      <= Test.sequence_id
  )


BTW, it would be interesting to see a *working* example using
ROW_NUMBER() (for tie-brakers) in combination with UPDATE.

Show quoteHide quote

On Wed, 14 Nov 2007 21:21:35 +0100, Gert-Jan Strik
<sorry@toomuchspamalready.nl> wrote:

Gert-Jan. I don't know what the issue may be with tie-breakers, but
this one at least works with the sample data.  If you know of data
that would not work I'd love to see it - this isn't the a feature I
know inside and out.

UPDATE Test
   SET document_2_seq_id = X.Sequence
  FROM (SELECT *,
               row_number()
               over (PARTITION BY journal_ctrl_num
               ORDER BY journal_ctrl_num, sequence_id ) as Sequence
          FROM Test) as X
WHERE Test.journal_ctrl_num = X.journal_ctrl_num
   AND Test.sequence_id = X.sequence_id
   AND Test.document_2 = X.document_2

Roy Harvey
Beacon Falls, CT

Ah, easy as that! I guess the trick is in using a derived table. I tried
with just an UPDATE statement (no SELECT), which would not work.

This solution is better. It handles tie-breakers better (in such a the
solution I posted will hand out the same "rownumber" twice). Also,
potentially, this solution performs much better.

Thanks,
Gert-Jan


Show quoteHide quote

On Wed, 14 Nov 2007 22:17:58 +0100, Gert-Jan Strik
<sorry@toomuchspamalready.nl> wrote:

To tell you the truth I didn't know how to write it when I started,
only that it could be done.  So I started the way I always start with
any complicated UPDATE, I wrote a SELECT that produced the values that
I needed.  After that using a derived table just sort of happened.

Roy Harvey
Beacon Falls, CT

The concept of 'tie-breakers' does not exist in row_number.
The row_number gurantees a unique value over N rows for each
row regardless of the appropriateness of the ORDER BY. The
values come from opening a cursor on the table. The values
come from each row of the cursor regardless of how the cursor
is defined. In other words, the only requirement is that
the cursor can be opened. This is the 'sequential' idea in bol
and why the following row_number is perfectly valid:

SELECT *,
               row_number()
               over (PARTITION BY journal_ctrl_num
               ORDER BY journal_ctrl_num) as Sequence
          FROM Test

The row_number is conceptually close to the idea of an identity/surrogate
key in that it can be guranteed to be unique and guranteed to be totally
meaningless. The above query is non-deterministic, the optimizer is free
to choose any order for the cursor within a partition. Row_number should
be used with great caution by application developers. Rank(), on the other
hand,
is not a direct result of a cursor declaration. Rather it is a measure
of how good the values of the ORDER BY columns can target the rows of
the cursor. Same value ranks represent ambiguity concerning the targeting
of rows. Rank is conceptually close to the idea of a key. Unique values
of rank must be a key of the table. That rank should always be used over
row_number is the same debate as natural keys vs. identity. Make sense?:)
I'll have much more on ranking and ORDER BY in an upcoming article:)

www.beyondsql.blogspot.com

Thank you all so much for your responses!  This helped a lot!

Anyways, the code below solved my problem! 

UPDATE tempgltrx
   SET document_2_seq_id = X.Sequence
  FROM (SELECT *,
               row_number()
               over (PARTITION BY journal_ctrl_num, document_2
               ORDER BY journal_ctrl_num, sequence_id ) as Sequence
          FROM tempgltrx) as X
WHERE tempgltrx.journal_ctrl_num = X.journal_ctrl_num
   AND tempgltrx.sequence_id = X.sequence_id
   AND tempgltrx.document_2 = X.document_2

Thanks again to all of you!
Dee

Show quoteHide quote

Poor SQL Server Performance on High End System
How Does Log Shipping / Database Mirroring Work with a SAN?
Nic Card Preferences
Dts package that imports difference
How does UPDATE statement work?