Too Many Rows Were Affected error on updating a Transact SQL linked table

Question

I am trying to update a linked table with this...

update openquery (LINKSERVERID, 'select tng_email from user_list where tng_id = 62873') 
  set tng_email = '[email protected]';

... but I get the following error ...

OLE DB provider "MSDASQL" for linked server "LINKSERVERID" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".

FYI: tng_id is the primary key.

How do I fix?

Answer 1

I think you need to include the key in the select query, so try this:

update openquery(
  LINKSERVERID, 'select tng_id, tng_email from user_list where tng_id = 62873'
) set tng_email = '[email protected]';

Answer 2

I tried the answer above from jpw, but it didn't work for me.

I developed a trigger of which I also received the same error using the code below:

begin

    if TRIGGER_NESTLEVEL() > 1
    return

    declare @JCid       int =   (select top 1   iJCMasterID from inserted)

    begin
        update L set uiJCTxCMLineNumber = NewLineNum
        from (
        select
            rank() over (order by idJCTxLines) NewLineNum
        ,   iJCMasterID
        ,   uiJCTxCMLineNumber
        ,   idJCTxLines
        from    _btblJCTxLines
        where iJCMasterID =  @JCid
        ) L
        where   iJCMasterID =  @JCid
    end
end
go

However, I resolved it by changing it to :

begin

    if TRIGGER_NESTLEVEL() > 1
    return

    declare @JCid       int =   (select top 1   iJCMasterID from inserted)

    begin
        update L set uiJCTxCMLineNumber = NewLineNum
        from (
        select
            rank() over (order by idJCTxLines) NewLineNum
        ,   iJCMasterID
        ,   uiJCTxCMLineNumber
        ,   idJCTxLines
        from    _btblJCTxLines
        where iJCMasterID =  @JCid
        ) L
    join inserted i on L.idJCTxLines = i.idJCTxLines
    end
end
go

Hope this helps.

Answer 3

I solved this error by adding a unique index to the underlying table.