Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: MERGE statement into two tables

    Hi,

    I am trying to update values in two tables (approx 1-2 million records).

    I have decided to use a MERGE statement, however have been unsuccessful to do so.

    Below is an example of the statement.

    The error is receive is "Incorrect syntax near '('." right after the MERGE line.

    Is it not possible to use this syntax ?

    Thanks!


    Example:
    [CODE]
    MERGE
    ( SELECT a.id, b.id2
    FROM TableA A
    JOIN TableB B
    ON (a.join =b.join)
    ) target
    USING
    ( SELECT c.id, d.id2
    FROM TableC C
    JOIN TableD D
    ON (c.join =d.join)
    ) source
    ON (source.id = target.id)

    WHEN MATCHED THEN
    UPDATE
    blah balh
    [\CODE]

  2. #2
    Join Date
    Sep 2003
    Posts
    102
    I tried substituting a View as the Target Table and received the below error.

    I am assuming that it will not be possible ?

    Thanks!

    Msg 4405, Level 16, State 1, Line 7
    View or function 'target' is not updatable because the modification affects multiple base tables.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can't use a SELECT as a target. See the syntax for MERGE (Transact-SQL)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can put an INSTEAD OF TRIGGER on your view, but I'd recommend splitting your MERGE into multiple statements instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by blindman View Post
    You can put an INSTEAD OF TRIGGER on your view, but I'd recommend splitting your MERGE into multiple statements instead.
    Thanks all.

    I ended up using the Triggers on the view.

    Why would you recommend splitting the MERGE ?

    Would it be faster to do a MERGE then an Update based on the Merged data rather than doing one Merge statement on a View that uses Triggers ?

    Some details:

    Approx 1.5 million records will be updated in the merge.
    In the MERGE statement-
    'WHEN MATCHED', will do an update to one table.
    'WHEN NOT MATCHED" will do an insert into two tables.


    The likelihood of 'NOT MATCHED' is fairly low (~ < 5%).

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because triggers on views are awkward and distasteful. That's why.
    I'm a big proponent of triggers, but they should be used primarily for data-level logic, and your update process is a step or two away from that.
    Speed is not the issue here, or shouldn't be, if you're only dealing with 1.5 million records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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