MERGE Command - Sub Query in the UPDATE Part?

Julie · Post by **Julie** » Thu Aug 29, 2013 2:31 pm

Hi guys,

I have a question about the MERGE command. I understand how this example works:

dbadmin=> select * from m_test;
 col1 | col2 | col3
------+------+------
(0 rows)

dbadmin=> select * from m_test1;
 col1 | col2
------+------
    1 | A
(1 row)

Code: Select all

dbadmin=> merge into m_test m using m_test1 m1 on m1.col1 = m.col1
dbadmin->   when matched then update set
dbadmin->     col2 = m1.col2,
dbadmin->     col3 = NULL
dbadmin->   when not matched then insert (
dbadmin(>     col1,
dbadmin(>     col2,
dbadmin(>     col3) VALUES (
dbadmin(>     m1.col1,
dbadmin(>     m1.col2,
dbadmin(>     NULL);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from m_test;
 col1 | col2 | col3
------+------+------
    1 | A    |
(1 row)

But what if I want to look up a value for the col3 column from another table in the MERGE command? It seems like I can do this in the UPDATE part of the MERGE, but not the INSERT part? Is there a way I can do it in both?

Code: Select all

dbadmin=> select * from m_test;
 col1 | col2 | col3
------+------+------
    1 | A    |
(1 row)

dbadmin=> select * from m_test2;
 col2 | col3
------+-------
 A    | AAAAA
(1 row)

Code: Select all

dbadmin=> merge into m_test m using m_test1 m1 on m1.col1 = m.col1
dbadmin->   when matched then update set
dbadmin->     col2 = m1.col2,
dbadmin->     col3 = (select m2.col3 from m_test2 m2 where m2.col2 = m1.col2)
dbadmin->   when not matched then insert (
dbadmin(>     col1,
dbadmin(>     col2,
dbadmin(>     col3) VALUES (
dbadmin(>     m1.col1,
dbadmin(>     m1.col2,
dbadmin(>     NULL);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from m_test;
 col1 | col2 | col3
------+------+-------
    1 | A    | AAAAA
(1 row)

But the following does not work:

Code: Select all

dbadmin=> merge into m_test m using m_test1 m1 on m1.col1 = m.col1
dbadmin->   when matched then update set
dbadmin->     col2 = m1.col2,
dbadmin->     col3 = (select m2.col3 from m_test2 m2 where m2.col2 = m1.col2)
dbadmin->   when not matched then insert (
dbadmin(>     col1,
dbadmin(>     col2,
dbadmin(>     col3) VALUES (
dbadmin(>     m1.col1,
dbadmin(>     m1.col2,
dbadmin(>     (select m2.col3 from m_test2 m2 where m2.col2 = m1.col2));
ERROR 5238:  Unrecognized node type: 700

What does the error "ERROR 5238: Unrecognized node type: 700" mean?