Delete statement

[karpiv]

Code: Select all

EXEC ( 'CREATE TABLE test ( Id INT NOT NULL, nDoc INT )'
    ) AT TestPGNP

INSERT  INTO TestPGNP.TestPGNP.[public].test ( id, nDoc )
        SELECT  1,
                1
        UNION ALL
        SELECT  2,
                2
        UNION ALL
        SELECT  3,
                3
        UNION ALL
        SELECT  4,
                4

CREATE TABLE test
    (
      Id INT NOT NULL,
      nDoc INT
    )

INSERT  INTO test ( id, nDoc )
        SELECT  1,
                1
        UNION ALL
        SELECT  2,
                2
        UNION ALL
        SELECT  4,
                4

DELETE  RC
FROM    TestPGNP.TestPGNP.[public].test RC
        LEFT JOIN test F ON F.Id = RC.Id
WHERE   F.Id IS NULL

or

Code: Select all

DELETE  RC
FROM    TestPGNP.TestPGNP.[public].test RC
WHERE   NOT EXISTS ( SELECT 1
                     FROM   test F
                     WHERE  F.Id = RC.Id )

OLE DB provider "PGNP" for linked server "TestPGNP" returned message "Row cannot be located for deletion".
Msg 7345, Level 16, State 1, Line 2
The OLE DB provider "PGNP" for linked server "TestPGNP" could not delete from table ""TestPGNP"."public"."test""

[Moderator]

This defect is reproducible (it's been on the todo list for a while). We are working on the fix. Thank you!

[Moderator]

Actually, this behavior is by design, i.e. the provider requires a primary key to perform the deletion. Please change DDL as shown below:

Code: Select all

EXEC ( 'CREATE TABLE test ( Id INT NOT NULL, nDoc INT, CONSTRAINT pk_test_id PRIMARY KEY (id) )'
    ) AT TestPGNP

I have updated the diagnostic message to be more explicit:
Row cannot be located for deletion. Make sure the table has unique primary key.