Hello, I am trying to run the following delete statement:
DELETE OPENQUERY(oraclelinkedserver,'SELECT A.* FROM SCHEMAA.TABLE1 A LEFT JOIN SCHEMAB.TABLE2 B ON A.DODIC = B.DODIC AND B.STATUS = ''C'' WHERE B.DODIC IS NULL')
The linked server is an Oracle 11g server and I am running SQL Server 2008 R2.
I can do other simple delete openqueries but I can't figure out if its just the join it doesn't like or the naming conventions... I have tried changing the select list to be just the fields from table A but of course it doesn't like that cause the dodic field exists in both tables.
This is the error message:
OLE DB provider "OraOLEDB.Oracle" for linked server "oraclelinkedserver" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7345, Level 16, State 1, Line 1
Any ideas? Thanks!
delete openquery(linkedserver, 'select * FROM table1
WHERE dodic not in (select dodic from table2
where table2.dodic is not null and table2.status not in ('T', 'H', 'Q'))')
So, I guess openqueries just can't perform joins in a delete statement.
Thank you for trying to help though. :)