Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-02070 database does not support semi join in this context

798986Apr 5 2011 — edited Apr 5 2011
The following merge sql on Oracle 11g throws "ORA-02070 database does not support semi join in this context".

MERGE INTO ORDERS tgt
USING
(
SELECT C.short_name, C.customer_id
FROM customers C
WHERE customer_id IN ( SELECT distinct customer id FROM orders O 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;

Any ideas? This piece of code was working on an earlier version of Oracle 11g.

Thanks,
Anu

Comments

Frank Kulash
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
798986
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:
Frank Kulash
Hi,
user12201452 wrote:
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.
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.
kendenny
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;
Peter Gjelstrup
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
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 3 2011
Added on Apr 5 2011
5 comments
1,483 views