SQL & PL/SQL

Hi, Anu,

You can try this:

MERGE INTO ORDERS	tgt
USING
( 
        SELECT  C.short_name, C.customer_id
	FROM 	customers C
) 			src 
ON (	tgt.customer_id = src.customer_id
   AND	tgt.order_date	> SYSDATE - 3
   )
WHEN MATCHED THEN
     UPDATE  SET     tgt.short_name = src.short_name; 

It's surprising that the error message mentioned a semi-join, because you weren't doing a semi-join. An example of a semi-join is:

MERGE INTO ORDERS	tgt
USING
( 
        SELECT DISTINCT
	          C.short_name, C.customer_id
	FROM 	  customers C
	JOIN	  orders    o	ON  c.customer_id  = o.customer_id
	WHERE	  o.order_date	> SYSDATE - 3
) 			src 
ON (tgt.customer_id = src.customer_id)
WHEN MATCHED THEN
     UPDATE  SET     tgt.short_name = src.short_name; 

but perhaps the optimizer re-wrote your IN sub-query as a semi-join.

An EXISTS sub-query is another way to get the results you want, unless it causes ORA-02070, also. Natrually, I can't test anythihng, since you didn't post any sample data.

Edited by: Frank Kulash on Apr 5, 2011 11:34 AM

The explain plan for the select query does indicate a nested loop semi-join.

The query you suggested will not work as the inner query is actually (SELECT distinct customer id FROM orders O WHERE O.order_date > SYSDATE - 3 and short_name IS NULL) and Oracle does not allow columns referenced in the ON clause to be updated.

ORA-38104: Columns referenced in the ON Clause cannot be updated:

Hi, So put that condition in a WHERE clause, instead:

MERGE INTO ORDERS	tgt
USING
( 
        SELECT  C.short_name, C.customer_id
	FROM 	customers C
) 			src 
ON (tgt.customer_id = src.customer_id)
WHEN MATCHED THEN
     UPDATE  SET     tgt.short_name = src.short_name
     WHERE   tgt.order_date	> SYSDATE - 3
     AND     tgt.short_name	IS NULL

The condition "tgt.order_date > SYSDATE - 3" can go either in the USING clause or the WHERE clause.

Edited by: Frank Kulash on Apr 5, 2011 4:10 PM

Better yet, forget about MERGE. Use UPDATE, as Kendenny suggested below.

Another way to do it:

update orders o
   set o.short_name = (select short_name from customers c
                        where c.customer_id = o.customer_id)
 where o.order_date > sysdate - 3
   and o.short_name is null;

How about,

alter table orders drop column short_name;

Getting rid of the redundancy eliminates the need for this update, hence the error is gone. Voila!

(Also frees the mind of having to think about why orders.short_name is a customer's short_name)

Regards
Peter