Combining Two Tables With Oracle SQL

Question

I have two tables in the following structure:

TABLE 1:

ITEM  | JAN | FEB | MAR | APR | MAY | JUN
___________________________________________

Item A| 50  | 10  | 25  | NULL| NULL| NULL
Item C| 26  | 20  | 23  | NULL| NULL| NULL
Item B| 25  | 30  | 22  | NULL| NULL| NULL

TABLE 2:

ITEM  | JAN | FEB | MAR | APR | MAY | JUN
___________________________________________

Item A| NULL| NULL| NULL|  32 | 26  | 12
Item B| NULL| NULL| NULL|  25 | 24  | 10
Item D| NULL| NULL| NULL|  22 | 35  | 14

I am trying to merge the tables, to get the following result:

ITEM  | JAN | FEB | MAR | APR | MAY | JUN
___________________________________________

Item A| 50  | 10  | 25  |  32 | 26  | 12
Item B| 25  | 30  | 22  |  25 | 24  | 10
Item C| 26  | 20  | 23  | NULL| NULL| NULL
Item D| NULL| NULL| NULL|  22 | 35  | 14

I tried the following query:

MERGE INTO TABLE1 a USING (
  SELECT REBATE_ITEM, JAN, FEB, MAR, APR, MAY  FROM TABLE2
)  b
ON (TRIM(a.ITEM) = TRIM(b.ITEM) AND a.JUN is null)
WHEN  MATCHED THEN
  UPDATE SET 
    a.APR = b.APR, 
    a.MAY = b.MAY,     
    a.JUN = b.JUN

I get the following result: SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated:

Any ideas on how I can accomplish this merge/join/whatever?

MERGE updates a table, your description sounds as if simply want a query that returns the desired result. What is it exactly that you want to achieve? — user330315
– user330315, Commented Aug 23, 2013 at 17:24
What should happen when an Item has a value for one month in both tables where those values are different? — Declan_K
– Declan_K, Commented Aug 23, 2013 at 17:25

Tricky12 · Accepted Answer · 2013-08-23 18:31:11Z

8

Give this a try, I think it should work for what you are trying to do.

MERGE INTO TABLE1 a USING (
    SELECT ITEM, JAN, FEB, MAR, APR, MAY, JUN
    FROM TABLE2) b
ON (a.ITEM = b.ITEM)
WHEN MATCHED THEN
    UPDATE SET
        a.APR = b.APR,
        a.MAY = b.MAY,
        a.JUN = b.JUN
    WHERE a.JUN = null
WHEN NOT MATCHED THEN
    INSERT (a.ITEM, a.JAN, a.FEB, a.MAR, a.APR, a.MAY, a.JUN)
    VALUES (b.ITEM, b.JAN, b.FEB, b.MAR, b.APR, b.MAY, b.JUN);

answered Aug 23, 2013 at 18:31

Tricky12

6,8301 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2013-08-23 17:26:30Z

1

If you just want to select the values (rather than actually changing data in the database) you can use a union and a group by:

select ITEM, 
       sum(JAN) as jan, 
       sum(FEB) as feb, 
       sum(MAR) as mar,
       sum(APR) as apr,
       sum(may) as may  
       sum(JUN) as jun
from (
   select ITEM, JAN, FEB, MAR, APR, MAY, JUN
   from table1
   union all
   select ITEM, JAN, FEB, MAR, APR, MAY, JUN
   from table2
) t
group by item;

answered Aug 23, 2013 at 17:26

user330315

Collectives™ on Stack Overflow

Combining Two Tables With Oracle SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related