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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

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

  1. #1
    Jon Cohen Guest

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


    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
    Share on Google+

  2. #2
    Bill Vaughn Guest

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


    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


    Share on Google+

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 05:14 PM
  2. Upgrading SQL Server 6.5 to 7.0
    By blue in forum Database
    Replies: 2
    Last Post: 02-28-2001, 10:42 AM
  3. SQL Server migration from 6.5 to 7.0
    By Shantha Gowda in forum Database
    Replies: 1
    Last Post: 12-07-2000, 10:18 AM
  4. SQL server administrative knowledge
    By Becky in forum Database
    Replies: 1
    Last Post: 07-11-2000, 04:27 PM
  5. Re: Sort problem in MS SQL Server 7.0
    By D. Patrick Hoerter in forum Database
    Replies: 1
    Last Post: 06-26-2000, 05:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center