CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-delete
user3789200
user3789200

Reputation: 1186

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

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.

Upvotes: 0

Views: 75

Answers (1)

user330315
user330315

Reputation:

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);

Upvotes: 2

Related Questions