Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: ORA-38104 MERGE statement

    Oracle 11g

    Requirement: Update column after using it in matching condition.

    tables:

    CREATE TABLE "CONV_NL"."COPCS"
    ( "ACCOUNT__C" VARCHAR2(50 CHAR),
    "LEGACY_SYSTEM_ID__C" VARCHAR2(255 CHAR),
    "SFDC_ID" VARCHAR2(20 CHAR)

    CREATE TABLE "CONV_NL"."ACCS"
    (
    "LEGACY_SYSTEM_ID__C" VARCHAR2(30 CHAR),
    "SFDC_ID" VARCHAR2(20 CHAR)

    Merge query:
    MERGE INTO CONV_NL.COPCS a
    USING (select x.SFDC_ID SFDC_ID, x.LEGACY_SYSTEM_ID__C LEGACY_SYSTEM_ID__C from CONV_NL.ACCS x) b
    ON (a.ACCOUNT__C=b.LEGACY_SYSTEM_ID__C)
    when matched then
    update set a.ACCOUNT__C=b.SFDC_ID

    Please help with any workarounds! Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please help with any workarounds!
    I do not understand what the correct solution would look like.

    provide written explanation what the desired SQL statement should actually do.
    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
    Sep 2013
    Posts
    46
    The sql statement should update all the rows which were found in the ON() condition. But error says I can not update the column used in ON clause.. but that is the requirement..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE copcs a
    SET    a.account__c = (SELECT sfdc_id
                           FROM   accs x
                           WHERE  a.account__c = x.legacy_system_id__c)
    WHERE  a.account__c = (SELECT sfdc_id
                           FROM   accs x
                           WHERE  a.account__c = x.legacy_system_id__c)
    /
    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.

Posting Permissions

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