Getting the row id of a row in SQL Server 4.2 and 7.0

[Jon Cohen]

In a table of book titles I was working on today, I had a row that I wanted
to copy and change a couple of fields namely Title # and ISBN. Because, I
don't have a primary index on this table(I know bad developer, bad), I just
copied the record as such:

insert into titles
select * from titles where title_id = '99999'

This table had a timestamp field on it. So I thought it would update the
field. And I would be able to tell the difference between the 2 rows, by
looking for the most recently updated row. But, this did not work. It just
copied the field info from the source record.

Which brings me to my question, isn't there a way to get the row id of a
row in a table? And if so, can I perform DML statements on it using the row
id as the key? Something like ...

declare @targetrow int

select @targetrow=max(@@rowid) from titles
where title_id = '99999'

update titles
set isbn = '9-9999-9999-X'
where @@rowid = @targetrow


Thanks,
JC

[Bill Vaughn]

Not really. You need a primary key of some kind. An identity column, a column
you generate or ISBN. Then this kind of thing just works.

bv

"Jon Cohen" <jcohen@kensingtonbooks.com> wrote:
>
>In a table of book titles I was working on today, I had a row that I wanted
>to copy and change a couple of fields namely Title # and ISBN. Because,
I
>don't have a primary index on this table(I know bad developer, bad), I just
>copied the record as such:
>
>insert into titles
>select * from titles where title_id = '99999'
>
>This table had a timestamp field on it. So I thought it would update the
>field. And I would be able to tell the difference between the 2 rows, by
>looking for the most recently updated row. But, this did not work. It just
>copied the field info from the source record.
>
>Which brings me to my question, isn't there a way to get the row id of a
>row in a table? And if so, can I perform DML statements on it using the
row
>id as the key? Something like ...
>
>declare @targetrow int
>
>select @targetrow=max(@@rowid) from titles
>where title_id = '99999'
>
>update titles
>set isbn = '9-9999-9999-X'
>where @@rowid = @targetrow
>
>
>Thanks,
>JC