PL/SQL error ORA-00904: invalid identifier using merge, select and set

Question

I'm new to PL/SQL. I've created a table called orders which has a pk as orders_id and a products table with a pk as products_id. Both are numbers 1 through 10. In the orders table is a column called item_name, which I want to merge into the products column called item_name.

This is what I have tried:

MERGE INTO products
USING ( 
    SELECT item_name
    FROM orders) 
ON (orders.order_id = products.product_id)
WHEN MATCHED THEN 
UPDATE SET 
    products.item_name = orders.item_name

AND

MERGE INTO products
USING ( 
    SELECT item_name
    FROM orders) 
ON (products.product_id=orders.order_id )
WHEN MATCHED THEN 
UPDATE SET 
    products.item_name2 = orders.item_name

I keep getting the error "ORA-00904: "ORDERS"."ORDER_ID": invalid identifier". I've checked order_id is a NUMBER the same as the product_id. I have also created a column in products called item_name2, but that didn't work either. What does the error mean? Any help is appreciated, thank you.

Answer 1

Here's how.

Sample tables:

SQL> select * from orders;

 ORDERS_ID ITEM_N
---------- ------
         1 Name A
         2 Name B
         3 Name C
         5 Name E

SQL> select * from products;

PRODUCTS_ID ITEM_NAME
----------- ----------
          1
          2
          3
          4

Updating products.item_name from the orders table:

SQL> merge into products p
  2    using orders o
  3    on (o.orders_id = p.products_id)
  4  when matched then update set
  5    p.item_name = o.item_name;

3 rows merged.

Result:

SQL> select * from products;

PRODUCTS_ID ITEM_NAME
----------- ----------
          1 Name A
          2 Name B
          3 Name C
          4               --> no matching IDs, so it is left empty
 
SQL>