-
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
-
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
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|