Delete Statement

[acct98]

Does anyone know a simple way to get this delete statement to work?

SQL> DELETE
2 FROM Z
3 WHERE SSN||VENDORID = (SELECT SSN||VENDORID
4 FROM F)
5 /
WHERE SSN||VENDORID = (SELECT SSN||VENDORID
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

 

[BJCooperIT]

DELETE 
FROM Z
WHERE SSN||VENDORID IN (SELECT SSN||VENDORID FROM F)

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[sem]

I'm not familiar with your business needs, but are you sure you need concatenation, not pair compaison?

DELETE 
FROM Z
WHERE (SSN,VENDORID) IN (SELECT SSN,VENDORID FROM F)

I ask because this statement may utilize indexes on SSN and/or VENDORID. Your original statement ( or rather that one corrected by BJCooperIT) can use only special function-based index.

Regards, Dima

http://seminihin.narod.ru

 

[acct98]

I took out the concatenation and used the "IN" in the delete statement. Thanks for your help.