Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    13

    Unanswered: return a value from a MERGE?

    Howdy.

    I'm new to MERGE statements and I'm wondering if it is possible to return a value from either the MATCHED or NOT MATCHED portion of one of them.

    I have a dumbed down example below:

    MERGE INTO USERS
    USING (SELECT FIRST_NAME, LAST_NAME, EMAIL
    FROM USERS
    WHERE USERNAME = pUsername)
    ON (USERS.USERNAME = pUsername)
    WHEN MATCHED THEN
    UPDATE SET
    FIRST_NAME = pFName,
    LAST_NAME = pLName,
    EMAIL = pEmail
    WHEN NOT MATCHED THEN
    INSERT (ID, FIRST_NAME, LAST_NAME,
    EMAIL, ENTRY_BY, ENTRY_DATE,
    USERNAME)
    VALUES (pId, pFName, pLName,
    pEmail, pUsername, SYSDATE, pUsername);

    What I would like to do is return a NUMBER from both of the conditions...a 1 for the MATCH and a 0 for the NOT MATCHED to keep it simple.

    Is there anyway to do that?

    Many thanks,
    bryan

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, but since you are only inserting/updating a single row you could do this:
    Code:
    begin
      insert into users (id, first_name, last_name, email, entry_by, entry_date, username)
      values (pid, pfname, plname, pemail, pusername, sysdate, pusername);
      l_match := 0;
    exception
      when dup_val_on_index then
        update users set first_name = pfname, last_name = plname, email = pemail
        where users.username = pusername;
        l_match := 1;
    end;

  3. #3
    Join Date
    Sep 2002
    Posts
    13
    True, but my main question was whether or not you could return a value from a merge...that was merely a dumbed down example.

    However, since the answer is no that definitely answers my question. Thanks!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Consider: in general a merge can insert many rows and update many rows. How could it there meaningfully return "a value" to indicate whether it MATCHED or NOT MATCHED?

Posting Permissions

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