Delete statement

Report PGNP bugs.

Delete statement

Postby karpiv » Tue Jan 19, 2010 4:58 am

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""
Last edited by karpiv on Wed Jan 20, 2010 3:22 am, edited 1 time in total.
karpiv
 
Posts: 6
Joined: Wed Dec 02, 2009 7:24 am

Re: Delete statement

Postby Moderator » Tue Jan 19, 2010 11:27 pm

This defect is reproducible (it's been on the todo list for a while). We are working on the fix. Thank you!
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: Delete statement

Postby Moderator » Tue Jun 01, 2010 12:15 am

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.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm


Return to Report Bugs

Who is online

Users browsing this forum: No registered users and 1 guest

cron