Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Red face Unanswered: "Delete and insert"

    I want to replace table data with new records if they exist and to insert the data if they do not. There is no unique keys.

    For example

    Table data:
    ABC
    ABC
    BCD
    DEF

    New records:
    ABC
    BCD
    BCD
    EFG

    Data wanted after insert/delete:

    ABC
    BCD
    BCD
    DEF
    EFG

    As you can see, the record ABC which was represented twice in the original data is replaced by one record after the "update". And BCD is now two records instead of one. DEF is not touched and EFG is added.

    How do I do that?

    I have tried with a before insert trigger, which should delete existing records before inserting new ones, but it mutates.

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    Look for MERGE statement see here

    Oracle Merge SQL Insert Update Upsert

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    Thank you for a quick response. But I am afraid that it won't solve my problem. I still need to create a trigger and this trigger will still mutate

    When I try to delete all the ABC-records (in the trigger) because I then also deletes the newly inserted ABC-records. It could be 6 times ABC which should replace original 8 times ABC and vice versa.

  4. #4
    Join Date
    Nov 2006
    Posts
    82
    Can you post here trigger definition.

  5. #5
    Join Date
    Jun 2003
    Posts
    81
    CREATE TRIGGER table1_before_ins
    before insert ON TABLE1 for each row
    begin
    -- delete if exists
    DELETE FROM TABLE1 WHERE
    COLUMN1 = :NEW.COLUMN1;
    end;
    /

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I believe that you can accomplish this with INSTEAD OF triggers. As this kind of triggers are on views only, you may:
    - rename your table from A to A1
    - create a view named A as SELECt * FROM A1
    - create INSTEAD OF triggers on A
    - any DML sent to the A-table now goes to the A-view and fires the triggers
    - make sure that no DML goes directly to A1-table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The problem here might be the fact that, in a relational table, there's no "first" record or "seventh" record - they are like apples in a basket.

    What you (kfc) suggest is that "the first ABC in the first set is compared to the first ABC in the second set" etc. That most probably won't happen because there's nothing that says which record is which.

    So, are there any other columns in those tables (so that you could compare "new" to "old" values)?

Posting Permissions

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