Post reply

use UPDATE with OPENQUERY on mutiple columns

  • June 17, 2018 at 3:25 pm

    #351822

    I'm trying to update a table in mysql from SQL SERVER:

    UPDATE OPENQUERY(SRVLINK,'Select COLA, COLB, COLC from rtable

    WHERE id = 157') set COLA = 2 ,
    COLB= 'text', COLC = 'test'

    id is the pK from the remote table (rtable)

    I get this error:

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

    Basically I don't even need to select those columns, just update them by the index, is there a better way to achieve this ?

    (writing a direct update query didn't work with openquery for some reason)

  • June 18, 2018 at 6:27 am

    #1994269

    kaplan.dani - Sunday, June 17, 2018 3:25 PM

    I'm trying to update a table in mysql from SQL SERVER:

    UPDATE OPENQUERY(SRVLINK,'Select COLA, COLB, COLC from rtable

    WHERE id = 157') set COLA = 2 ,
    COLB= 'text', COLC = 'test'

    id is the pK from the remote table (rtable)

    I get this error:

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

    Basically I don't even need to select those columns, just update them by the index, is there a better way to achieve this ?

    (writing a direct update query didn't work with openquery for some reason)

    Try adding the id column to your select list.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply