Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337

    Unanswered: Merge deletes w/o updates

    I have a bridge table with 3 columns. Pk is (a,b,c) so I need all 3 columns when joining for the merge statement. I only need to insert and delete. The problem is that delete statement is an optional part of the update statement. You can't delete with out having an update statement and you can't update any of the columns because they are all used in the on clause.

    Is there any way I can do a noop update statement that will allow me to use a merge statement here?

    Code:
    MERGE INTO foo f
    USING (
      ... XMLTABLE stuff here ...
    ) x ON (f.a = x.a AND f.b = x.b AND f.c = x.c)
    WHEN MATCHED THEN 
      UPDATE SET -- required syntax
        -- need noop here and can't reference a, b, or c
      DELETE WHERE x.del = 'Y'
    WHEN NOT MATCHED THEN INSERT (a,b,c)
      VALUES (x.a, x.b, x.c)
    MERGE

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    MERGE

    "Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion."

    Just UPDATE rows being DELETEd.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Right that normally works. But in this case I need to reference all three columns in the on clause. And you can't update any of the columns used in the on clause. But you need to update SOMETHING, hence the problem.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    it's also called an "upsert" for a reason.
    if you don't need to update then I wouldn't use a merge.

    direct inserts will be faster and straight delete will be faster.
    it's when you need to update PLUS delete/insert that you should leverage merge (possibly).

    You could fudge it by updating a column with it's own value if you really wanted, but I
    don't see the benefit.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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