Results 1 to 5 of 5

Thread: Delete Identity

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Delete Identity

    I Can use this too get the Identity of an Insert Command
    select IdentityDelete=@@identity

    What do I Use to Get the Identity of a Delete Command

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm... Maybe I missed something here, but wouldn't you need the identity in order to actually DELETE the row?

    -PatP

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    The long way round yeah I could, Im deleting the oldest comment to be replaced with the newest like a rollover.

    cmdDelete.CommandText = "DELETE FROM Comment WHERE iCommentID IN(" _
    & "SELECT min(iCommentID) FROM Comment WHERE iListingID = " + Comment.ListingID.ToString + ")"

    The comments I use have ParentId's (FK) which if used means that the Comment is a reply to another comment. If I delete the Parent Comment I want to delete any replies so that the data isn't irrelevant. Im deleting the comments regarding some business rules won't make to much sense its not really a forum like this or other common designs.

    I can do it the long way round but would like to do it with as little DB requests as possible.

    eg @@identity if its possible

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Sorry Got it just had to look at it for 5 min

    SELECT * FROM Comment
    WHERE iCommentID IN
    (SELECT min(iCommentID) FROM Comment
    WHERE iListingID = 4) OR iParentID IN
    (SELECT min(iCommentID) FROM Comment
    WHERE iListingID = 4)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I'm way off base, but this smells like it ought to be an UPDATE operation instead of a DELETE then an INSERT to me.

    -PatP

Posting Permissions

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