CODEWITHSUNDEEP
sqloracle-database
mayumi Tai
mayumi Tai

Reputation: 55

MERGE INTO update failure

I have two queries. One returns this output:

SQL > select * from TableA

id | stable |    a    |    l    |    N    |  user   |   date  |  Ouser  |  Odate |
-----------------------------------------------------------------------------------
x  | aa     |   GHUA  |   0000  |   1000  |  userA  |   1223  |  userA  |   1223  |
y  | bbb    |   GHUA  |   1111  |   2000  |  userB  |   1223  |  userA  |   1223  |
z  | ccc    |   GHUA  |   2222  |   3000  |  userC  |   1223  |  userA  |   1223  |

The other returns this:

SQL > select * from TableB

id | stable |    a    |    l    |    N    |  user   |   date  |  Ouser  |   Odate |
-----------------------------------------------------------------------------------
A  | abe    |   GHUA  |   3000  |   4100  |  userD  |   1224  |  userB  |   1223  |
B  | aa     |   GHUA  |   0000  |   1100  |  userC  |   1224  |  userC  |   1223  |
C  | bbb    |   GHUA  |   1111  |   2100  |  userD  |   1224  |  userD  |   1223  |
D  | ccc    |   GHUA  |   2222  |   3300  |  userE  |   1224  |  userE  |   1223  |

========================================================================

I want to use merge into to change TableA with the data from TableB ,but always update/Insert failure.

Could someone give some advice for me?

merge into TableA d 
      using TableB s 
         on (trim(d.stable) = trim(s.stable) and trim(d.a) = trim(s.a) and 
trim(d.l) = trim(s.l)) 
when MATCHED then 
        update set (d.n = trim(s.n),d.user= s.user,d.date= s.date)
        when NOT MATCHED then 
           insert (ID,stable,a,l,n,user,date,Ouser,Odate)
           values(ID,stable,a,l,n,user,date,Ouser,Odate);

Upvotes: 0

Views: 704

Answers (1)

APC
APC

Reputation: 146219

When I ran your code it hurled this error:

ORA-01747: invalid user.table.column, table.column, or column specification

This is because you have erroneously wrapped the SET clause in brackets. You need to remove them.

Having done that the code hurls:

ORA-38101: Invalid column in the INSERT VALUES Clause: "D"."ID"

This is because your source and target tables have the same projection, so the column names in your INSERT statement are defined ambiguously. You may wish to argue that the compiler ought to know that columns in the INSERT() projection refer to tableA and columns in the VALUES() projection refer to tableb. Whatever, we have to obey the compiler. So you need to qualify the column names.

This version of your code works:

merge into TableA d 
      using TableB s 
         on (trim(d.stable) = trim(s.stable) 
         and trim(d.a) = trim(s.a) 
         and trim(d.l) = trim(s.l)) 
when MATCHED then 
        update 
        -- remove unnecessary brackets
        set d.n = trim(s.n)
             ,d.usr= s.usr
             ,d.dt= s.dt 
when NOT MATCHED then 
        -- alias all the columns
        insert (d.ID,d.stable,d.a,d.l,d.n,d.usr,d.dt,d.Ouser,d.Odate)
        values (s.ID,s.stable,s.a,s.l,s.n,s.usr,s.dt,s.Ouser,s.Odate);

Upvotes: 4

Related Questions