DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Need help with Update statement

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Need help with Update statement

    Greetings,

    I have a lot of records in a table with the following format:

    ITEM SOURCE DEST EFFECTIVE[B]
    123 A B 09/15/03
    123 A B 09/20/03
    456 D E 09/10/03
    456 D E 09/12/03

    I need to update one row for each distinct ITEM/SOURCE/DEST with an EFFECTIVE date of say 12/01/03. This table has a primary key of ITEM/SOURCE/DEST/EFFECTIVE, so I cannot update all the rows at once because of the primary key violation. What I'd like to do is, for each distinct ITEM/SOURCE/DEST, update its EFFECTIVE to 12/01/03, and then I can come back and delete all the rows that <> 12/01/03.

    Does anyone know how I can do this?

    Steve

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Are you looking to just delete duplicate combination of ITEM/SOURCE/DEST? If so, you can just delete them directly without doing update on them first and then delete the rest.

    Following SQL can help :

    delete from your_table
    where rowid <> ( select max(rowid)
    from your_table tab_alias
    where your_table.ITEM = tab_alias.ITEM
    and your_table.SOURCE tab_alias.SOURCE
    and your_table.DEST = tab_alias.DEST );

    HTH.
    -- Dilip

  3. #3
    Join Date
    Aug 2003
    Posts
    100
    That worked...THANKS!

Posting Permissions

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


Click Here to Expand Forum to Full Width