Home » SQL & PL/SQL » SQL & PL/SQL » Can you guyz check this code plz !!!
Can you guyz check this code plz !!! [message #238225] Thu, 17 May 2007 02:39 Go to next message
Cuong
Messages: 12
Registered: May 2007
Junior Member
here is my code

CREATE TABLE DWPRODUCT AS
SELECT p.product_id, p.product_name, p.category, p.weight, p.manufacturer
FROM SAUSPRODUCT p
WHERE 1=0;

ALTER TABLE DWPRODUCT ADD
(product_key NUMBER CONSTRAINT PK_DWPRODUCT PRIMARY KEY);
-----------------------------------------------
MERGE INTO DWPRODUCT
USING SAUSPRODUCT s
ON (dwproduct.product_id = s.product_id )
WHEN MATCHED THEN UPDATE SET dwproduct.product_id = s.product_id -- SKIP
WHEN NOT MATCHED THEN INSERT (product_key, product_id, product_name, category, weight, manufacturer)
VALUES (custkey_seq.NEXTVAL, s.product_id, s.product_name, s.category, s.weight, s.manufacturer );

And i keep getting this error message

ON (dwproduct.product_id = s.product_id )
*

ERROR at line 3:
ORA-00904: "DWPRODUCT"."PRODUCT_ID": invalid identifier

regard
Re: Can you guyz check this code plz !!! [message #238230 is a reply to message #238225] Thu, 17 May 2007 02:49 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's probably this line:
WHEN MATCHED THEN UPDATE SET dwproduct.product_id = s.product_id

In a MERGE you can't update the column that you join on.

Try this:
MERGE INTO DWPRODUCT 
USING SAUSPRODUCT s
ON (dwproduct.product_id = s.product_id )
WHEN MATCHED THEN update set dwproduct.product_name = dwproduct.product_name -- SKIP
WHEN NOT MATCHED THEN INSERT (product_key, product_id, product_name, category, weight, manufacturer)
VALUES (custkey_seq.NEXTVAL, s.product_id, s.product_name, s.category, s.weight, s.manufacturer );
Previous Topic: Correlated sub-query
Next Topic: order by the value from column
Goto Forum:
  


Current Time: Sat Dec 10 08:51:12 CST 2016

Total time taken to generate the page: 0.12963 seconds