OraFAQ Forum: SQL & PL/SQL » merge statement (merged :)

Home » SQL & PL/SQL » SQL & PL/SQL » merge statement (merged :)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

merge statement (merged :) [message #642787]

Sat, 19 September 2015 05:57

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

I have 2 tables PHOTO_URL_STAGING_MERGE and CORE_PHOTO_URLS. urls can be added, deleted or changed
I have the below merge statement, in the when matched clause I am trying to capture where the property_id and photo_num are the same,
but the url changed. when that happens I want to update the url. if I remove the "<>" it works, but update a lot of urls that do not need to be updated
with the "<>" it is not recognizing the urls that actually changed. any help would be appreciated.

SQL> desc PHOTO_URL_STAGING_MERGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROP_ID                                   NOT NULL NUMBER(15)
 PHOTO_NUM                                          NUMBER(8)
 PHOTO_URL                                          VARCHAR2(256)
 LISTNUM                                   NOT NULL VARCHAR2(40)
 SOURCE                                    NOT NULL NUMBER(10)

SQL> desc CORE_PHOTO_URLS_TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_ID                               NOT NULL NUMBER(15)
 PHOTO_URL_NUM                             NOT NULL NUMBER(5)
 PHOTO_URL                                 NOT NULL VARCHAR2(768)
 RETS_MEDIA_KEY                                     VARCHAR2(150)
 CAPTION                                            VARCHAR2(1500)
 DESCRIPTION                                        VARCHAR2(768)

merge statement

MERGE INTO CORE_PHOTO_URLS_TEST A
       USING(SELECT  PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE
                FROM photo_url_staging_merge) b
               ON ( A.PROPERTY_ID = b.prop_id
               AND A.PHOTO_URL_NUM = b.PHOTO_NUM)
        WHEN MATCHED THEN
            UPDATE
               SET A.PHOTO_URL = b.PHOTO_URL
             WHERE A.PHOTO_URL <> b.PHOTO_URL  
        WHEN NOT MATCHED THEN
            INSERT 
                (PROPERTY_ID, PHOTO_URL_NUM, PHOTO_URL)
            VALUES
                (b.PROP_ID,b.PHOTO_NUM,b.PHOTO_URL);

Report message to a moderator

merge statement [message #642788 is a reply to message #642787]

Sat, 19 September 2015 05:58

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

I have 2 tables PHOTO_URL_STAGING_MERGE and CORE_PHOTO_URLS. urls can be added, deleted or changed
I have the below merge statement, in the when matched clause I am trying to capture where the property_id and photo_num are the same,
but the url changed. when that happens I want to update the url. if I remove the "<>" it works, but update a lot of urls that do not need to be updated
with the "<>" it is not recognizing the urls that actually changed. any help would be appreciated.

SQL> desc PHOTO_URL_STAGING_MERGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROP_ID                                   NOT NULL NUMBER(15)
 PHOTO_NUM                                          NUMBER(8)
 PHOTO_URL                                          VARCHAR2(256)
 LISTNUM                                   NOT NULL VARCHAR2(40)
 SOURCE                                    NOT NULL NUMBER(10)

SQL> desc CORE_PHOTO_URLS_TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_ID                               NOT NULL NUMBER(15)
 PHOTO_URL_NUM                             NOT NULL NUMBER(5)
 PHOTO_URL                                 NOT NULL VARCHAR2(768)
 RETS_MEDIA_KEY                                     VARCHAR2(150)
 CAPTION                                            VARCHAR2(1500)
 DESCRIPTION                                        VARCHAR2(768)

merge statement

MERGE INTO CORE_PHOTO_URLS_TEST A
       USING(SELECT  PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE
                FROM photo_url_staging_merge) b
               ON ( A.PROPERTY_ID = b.prop_id
               AND A.PHOTO_URL_NUM = b.PHOTO_NUM)
        WHEN MATCHED THEN
            UPDATE
               SET A.PHOTO_URL = b.PHOTO_URL
             WHERE A.PHOTO_URL <> b.PHOTO_URL  
        WHEN NOT MATCHED THEN
            INSERT 
                (PROPERTY_ID, PHOTO_URL_NUM, PHOTO_URL)
            VALUES
                (b.PROP_ID,b.PHOTO_NUM,b.PHOTO_URL);

Report message to a moderator

Re: merge statement (merged :) [message #642789 is a reply to message #642787]

Sat, 19 September 2015 06:19

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to include PHOTO_URL in the merge join clause.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: merge statement (merged :) [message #642797 is a reply to message #642789]

Sat, 19 September 2015 14:22

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

if I add photo_url to the on clause I get the following error. That is why I had it in the when matched clause. Attached in create table statements and insert statements. The goal is to have core_photo_urls match what is in photo_url_staging_merge.

MERGE INTO CORE_PHOTO_URLS_TEST A
USING(SELECT  PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE
        FROM photo_url_staging_merge) b
   ON ( A.PROPERTY_ID = b.prop_id
        AND A.PHOTO_URL_NUM = b.PHOTO_NUM
        and A.PHOTO_URL <> b.PHOTO_URL)
        WHEN MATCHED THEN
            UPDATE
               SET A.PHOTO_URL = b.PHOTO_URL
        WHEN NOT MATCHED THEN
            INSERT
                (PROPERTY_ID, PHOTO_URL_NUM, PHOTO_URL)
            VALUES
                (b.PROP_ID,b.PHOTO_NUM,b.PHOTO_URL);

ERROR at line 1:
ORA-38104: Columns referenced in the ON Clause cannot be updated:
"A1"."PHOTO_URL

Attachment: merge_info.txt
(Size: 3.59KB, Downloaded 1460 times)

Report message to a moderator

Re: merge statement (merged :) [message #642799 is a reply to message #642797]

Sat, 19 September 2015 16:22

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

MERGE
  INTO CORE_PHOTO_URLS_TEST A
  USING(
         SELECT  PROP_ID,
                 PHOTO_NUM,
                 PHOTO_URL,
                 LISTNUM,
                 SOURCE
           FROM  photo_url_staging_merge
       ) b
    ON (
            A.PROPERTY_ID = b.prop_id
        AND
            A.PHOTO_URL_NUM = b.PHOTO_NUM
       )
    WHEN MATCHED
      THEN
        UPDATE
           SET A.PHOTO_URL = b.PHOTO_URL
         WHERE A.PHOTO_URL <> b.PHOTO_URL
    WHEN NOT MATCHED
      THEN
       INSERT(PROPERTY_ID, PHOTO_URL_NUM, PHOTO_URL)
       VALUES(b.PROP_ID,b.PHOTO_NUM,b.PHOTO_URL)
/

SY.

Report message to a moderator

Re: merge statement (merged :) [message #642808 is a reply to message #642799]

Sun, 20 September 2015 19:15

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

Solomon,

this does the same as my first example. I am getting one row merged and not the changes to the first 2 urls

Report message to a moderator

Re: merge statement (merged :) [message #642809 is a reply to message #642808]

Sun, 20 September 2015 19:18

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

here are some create table and insert statements (sorry I had them in the attachment)

CREATE TABLE PHOTO_URL_STAGING_MERGE
(
  PROP_ID    NUMBER(15)                         NOT NULL,
  PHOTO_NUM  NUMBER(8),
  PHOTO_URL  VARCHAR2(256 BYTE),
  LISTNUM    VARCHAR2(40 BYTE)                  NOT NULL,
  SOURCE     NUMBER(10)                         NOT NULL
);




CREATE TABLE CORE_PHOTO_URLS_TEST
(
  PROP_ID         NUMBER(15)                    NOT NULL,
  PHOTO_URL       VARCHAR2(256 BYTE)            NOT NULL,
  PHOTO_URL_NUM   NUMBER(5)                     NOT NULL,
  CAPTION         VARCHAR2(500 BYTE),
  DESCRIPTION     VARCHAR2(256 BYTE),
  RETS_MEDIA_KEY  VARCHAR2(50 BYTE)
);


SET DEFINE OFF;
Insert into CORE_PHOTO_URLS_TEST
   (PROP_ID, PHOTO_URL, PHOTO_URL_NUM)
 Values
   (2024388831, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-1.jpg', 1);
Insert into CORE_PHOTO_URLS_TEST
   (PROP_ID, PHOTO_URL, PHOTO_URL_NUM)
 Values
   (2024388831, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-2.jpg', 2);
Insert into CORE_PHOTO_URLS_TEST
   (PROP_ID, PHOTO_URL, PHOTO_URL_NUM)
 Values
   (2024388831, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg', 3);
Insert into CORE_PHOTO_URLS_TEST
   (PROP_ID, PHOTO_URL, PHOTO_URL_NUM)
 Values
   (2024388831, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg', 4);
Insert into CORE_PHOTO_URLS_TEST
   (PROP_ID, PHOTO_URL, PHOTO_URL_NUM)
 Values
   (2024388831, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg', 5);
COMMIT;


SET DEFINE OFF;
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 1, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-1.jpg', '2151829', 1036);
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 2, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-2.jpg', '2151829', 1036);
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 3, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg', '2151829', 1036);
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 4, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg', '2151829', 1036);
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 5, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg', '2151829', 1036);
Insert into PHOTO_URL_STAGING_MERGE
   (PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE)
 Values
   (2024388831, 6, 'http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-6.jpg', '2151829', 1036);
COMMIT;

Report message to a moderator

Re: merge statement (merged :) [message #642811 is a reply to message #642809]

Mon, 21 September 2015 00:07

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What should be the result for these data?

Report message to a moderator

Re: merge statement (merged :) [message #642816 is a reply to message #642811]

Mon, 21 September 2015 01:52

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

Michel Cadot wrote on Mon, 21 September 2015 00:07

What should be the result for these data?

Michel,

I should be seeing in CORE_PHOTO_URLS_TEST, the first 2 rows updated and one row inserted.

Dan

Report message to a moderator

Re: merge statement (merged :) [message #642817 is a reply to message #642816]

Mon, 21 September 2015 02:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not tell show us.

Report message to a moderator

Re: merge statement (merged :) [message #642818 is a reply to message #642817]

Mon, 21 September 2015 02:12

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

current values CORE_PHOTO_URLS_TEST
2024388831 1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-1.jpg
2024388831 2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-2.jpg
2024388831 3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831 4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831 5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg

current values PHOTO_URL_STAGING_MERGE
2024388831 1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-1.jpg
2024388831 2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-2.jpg
2024388831 3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831 4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831 5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg
2024388831 6 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-6.jpg

merged values
2024388831 1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-1.jpg
2024388831 2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-2.jpg
2024388831 3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831 4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831 5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg
2024388831 6 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-6.jpg

[Updated on: Mon, 21 September 2015 02:14]

Report message to a moderator

Re: merge statement (merged :) [message #642819 is a reply to message #642818]

Mon, 21 September 2015 02:42

flyboy
Messages: 1903
Registered: November 2006

Senior Member

But that is exactly what the SQL you posted does (I only with renamed column CORE_PHOTO_URLS_TEST.PROPERTY_ID to PROP_ID, as it changed in the test case):

SQL> column photo_url format a80
SQL> select prop_id, photo_url_num, photo_url from CORE_PHOTO_URLS_TEST;

   PROP_ID PHOTO_URL_NUM PHOTO_URL
---------- ------------- --------------------------------------------------------------------------------
2024388831             1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-1.jpg
2024388831             2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-2.jpg
2024388831             3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831             4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831             5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg

SQL> select prop_id, photo_num, photo_url from PHOTO_URL_STAGING_MERGE;

   PROP_ID  PHOTO_NUM PHOTO_URL
---------- ---------- --------------------------------------------------------------------------------
2024388831          1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-1.jpg
2024388831          2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-2.jpg
2024388831          3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831          4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831          5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg
2024388831          6 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-6.jpg

6 rows selected.

SQL> MERGE INTO CORE_PHOTO_URLS_TEST A
  2  USING( SELECT  PROP_ID, PHOTO_NUM, PHOTO_URL, LISTNUM, SOURCE
  3         FROM  photo_url_staging_merge ) b
  4  ON ( A.PROP_ID = b.prop_id AND A.PHOTO_URL_NUM = b.PHOTO_NUM )
  5  WHEN MATCHED THEN
  6    UPDATE SET A.PHOTO_URL = b.PHOTO_URL
  7    WHERE A.PHOTO_URL <> b.PHOTO_URL
  8  WHEN NOT MATCHED THEN
  9    INSERT(PROP_ID, PHOTO_URL_NUM, PHOTO_URL)
 10    VALUES(b.PROP_ID,b.PHOTO_NUM,b.PHOTO_URL)
 11  /

3 rows merged.

SQL> select prop_id, photo_url_num, photo_url from CORE_PHOTO_URLS_TEST;

   PROP_ID PHOTO_URL_NUM PHOTO_URL
---------- ------------- --------------------------------------------------------------------------------
2024388831             1 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-1.jpg
2024388831             2 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_3-2.jpg
2024388831             3 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-3.jpg
2024388831             4 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-4.jpg
2024388831             5 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-5.jpg
2024388831             6 http://il-ccar.photos.mlsfinder.com/photos/2/1/5/1/8/2/9/_/2/2151829_2-6.jpg

6 rows selected.

Are there duplicate values for (PROP_ID, PHOTO_URL_NUM) in CORE_PHOTO_URLS_TEST? Your test case does not contain them - anyway defining primary key/unique constraints would forbid them.

Report message to a moderator

Re: merge statement (merged :) [message #642820 is a reply to message #642819]

Mon, 21 September 2015 02:45

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

the combination is the primary key.

Report message to a moderator

Re: merge statement (merged :) [message #642821 is a reply to message #642820]

Mon, 21 September 2015 02:49

flyboy
Messages: 1903
Registered: November 2006

Senior Member

sa_dwhite wrote on Mon, 21 September 2015 09:45

the combination is the primary key.

Why did you not put its definition (CONSTRAINT .. PRIMARY KEY) into the CREATE TABLE statement then?

Anyway, as you can see, the MERGE statement changes the tables as proposed. I see no problem with the primary key enabled.

Report message to a moderator

Re: merge statement (merged :) [message #642822 is a reply to message #642821]

Mon, 21 September 2015 02:51

sa_dwhite
Messages: 15
Registered: August 2013
Location: United States

Junior Member

the issue is the update never happens, the first 2 urls never change only the addition of the 6th url. the primary key has nothing to do with it

Report message to a moderator

Re: merge statement (merged :) [message #642824 is a reply to message #642822]

Mon, 21 September 2015 02:58

flyboy
Messages: 1903
Registered: November 2006

Senior Member

sa_dwhite wrote on Mon, 21 September 2015 09:51

the issue is the update never happens, the first 2 urls never change only the addition of the 6th url. the primary key has nothing to do with it

As you can see, two rows were updated when I have run that MERGE statemenet. I am on Oracle 11.2.0.3.0. What is your Oracle version? Can you run the same statements which I did and post results here?

Report message to a moderator

Previous Topic:	how to loop within sql statement without using PL/SQL Block
Next Topic:	How to select last rate of Product

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 15 11:56:35 CDT 2026