Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    Unanswered: Sql Server Deleting a record Problem

    Hi there

    I have big problem to Deleting a record in sql server 2000.

    my table has 3 fields like Eno,Ename,Esal and records are like
    Code:
     Eno     Ename    Esal
    ------  -----------   -------
     199     John       8999.00
     199     John       8999.00
     199     John       8999.00
     199     John       8999.00
    So now i want to delte record number 3 . if i use where class it will delete all records.

    How to Solve this problem But in Orcale we can delete using by RowID. But i don't think this feature is not available in Sqlserver2000.

    If any body has an idea Please Help me
    -- Prasad --

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    It's a relational database.
    These records are identicle so there is no way of distinguishing them.
    3rd record is a meaningless term.
    In fact you could say this is not a database by definitions.

    To delete a row (they are the same so it doesn't matter which).

    set rowcount 1
    delete tbl where Eno = 199
    set rowcount 0

  3. #3
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    Hi

    I think that the easiest way out of this is to add a identity column to our table, just an automatic counter(1,2,3...).

    Tip, when data is repeating it self in a table you should have look at your database design. Perhaps "John" only should exists one time in the table described and the money figures should be placed in a new table called money/salary or something?
    - Jonte

  4. #4
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    Thanks for reply

    hi
    If we add one more field with Identity property it will be occupying some space in database. That means unwantedly we are using memory. In the case of Oracle this ROWID is inbilt of Row Property. Why the Microsot come accross with this..

    Bye.
    -- Prasad --

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    What do you think ROWID does in Oracle? You don't see it but it is what Oracle calls "Over Head", it's there just the same taking up space. Don't blame Microsoft on poor database design.

  6. #6
    Join Date
    Feb 2002
    Posts
    4

    Talking

    Just think about lock escalation ...

  7. #7
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    hello Mr.achorozy

    Don't get angry I love Microsoft product. Past 1 year i have been working with sqlserver database.

    while running my ASp page unfortunatly we added 3 rows in table but i did't add any primary key in that table. If i added on primary key it won't create any problem for me.

    I think
    Set rowcount is best way to delete the record or update anything.

    any way thanks for u reply.
    -- Prasad --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •