Help needed with delete statement...

[MarkB2]

What is wrong with the following delete statement? I am trying to delete rows from a table on a linked server that exist in a table of the same name on the local server. The following is from query analyzer.

DELETE t_remote
   FROM SERVER.DATABASE.dbo.WEB_EOB t_remote
   WHERE EXISTS (SELECT eob_pk FROM web_eob t_local WHERE t_local.eob_pk = t_remote.eob_pk)
================
[red]Server: Msg 7306, Level 16, State 2, Line 23[/red]
Could not open table '"DATABASE"."dbo"."WEB_EOB"' from OLE DB provider 'SQLOLEDB'.  The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

I have tried many variations of the above without success. The only thing tricky is that the tables are named the same on the remote and local servers requiring the use of an alias. What am I missing?

I have an insert statement which follows the delete which inserts rows to the remote table from the local tables referenced in the delete statement. The insert works without a problem.

TIA
Mark

 

[donutman]

I think a Where In clause will be easier to understand. Doesn't the Where Exist have to be about the existence of a row in the remote table? You have it the other way. I'm not sure, because I don't use that construct very often.

[Blue]WHERE[/Blue] t_remote.eob_pk [Blue]IN[/Blue] 
   [Gray]([/Gray][Blue]SELECT[/Blue] eob_pk [Blue]FROM[/Blue] web_eob t_local 
       [Blue]WHERE[/Blue] t_local.eob_pk [Gray]=[/Gray] t_remote.eob_pk[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[MarkB2]

Thanx DonutMan...

Both solutions work actually. The problem turned out that the local web_eob table was empty. I should have checked that before issuing the delete or the subsequent insert statements.