Delete records in a table observing its primary keys using a select statement

0
votes

I'm unable to delete the rows of table A where its keys are WO_NO and ROW_NO. I wrote the following query but giving an error saying, invalid relational operation.

This is what I tried.

begin

DELETE FROM A
WHERE WO_NO,ROW_NO in (SELECT WO_NO,ROW_NO
FROM G1614617_1
MINUS
SELECT WO_NO,ROW_NO
FROM hirplk_test1);

dbms_output.put_line(SQL%ROWCOUNT);

end;
/

The select query returns the row values WO_NO and ROW_NO. but I cannot delete the records from tab A. Can someone please correct me.

1
sqloracleplsqlsql-delete

1 Answers

2
votes

You need to put the two columns between parentheses if you want to compare them with a two column sub-query:

DELETE FROM A
WHERE (WO_NO,ROW_NO) in (SELECT WO_NO,ROW_NO
                         FROM G1614617_1
                         MINUS
                         SELECT WO_NO,ROW_NO
                         FROM hirplk_test1);