Search code examples
mysqlsql-serveropenquery

OPENQUERY SQL Server MYSQL UPDATE

I have to work on a linked server. My goal: Update an entire table in mysql server(version:8.0.21) via OPENQUERY in SQL Server(version 13.0.1742.0). I tried this but it generates an error Row cannot be located for updating. Some values may have been changed since it was last read and this one The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

update linkedTable
set 
    linkedTable.id_parent=unlinkedTable.IdCat1,
    linkedTable.code=unlinkedTable.CodeFamilleFAT,
    linkedTable.niveau=unlinkedTable.NiveauCategorieFAT,
    linkedTable.langue=unlinkedTable.CodeLangueFAT,
    linkedTable.nom=unlinkedTable.LibelleCommercialFAT,
    linkedTable.descriptionA=unlinkedTable.DescriptifCom1FAT,
    linkedTable.vignette=null,
    linkedTable.id_categorie=unlinkedTable.id
from openquery(NAMELINKEDSERVER, 'select id_categorie, id_parent, code, niveau, langue, nom, description as descriptionA, vignette from DatabaseMySQL.Table') as linkedTable
inner join DatabaseSQLserver.dbo.Table as unlinkedTable on unlinkedTable.Id = linkedTable.id_categorie

Then I tried this:

update linkedTable
set 
    linkedTable.id_parent=unlinkedTable.IdCat1,
    linkedTable.code=unlinkedTable.CodeFamilleFAT,
    linkedTable.niveau=unlinkedTable.NiveauCategorieFAT,
    linkedTable.langue=unlinkedTable.CodeLangueFAT,
    linkedTable.nom=unlinkedTable.LibelleCommercialFAT,
    linkedTable.descriptionA=unlinkedTable.DescriptifCom1FAT,
    linkedTable.vignette=null,
    linkedTable.id_categorie=unlinkedTable.id
from openquery(NAMELINKEDSERVER, 'select id_categorie, id_parent, code, niveau, langue, nom, description as descriptionA, vignette from DatabaseMySQL.Table') as linkedTable
inner join DatabaseSQLserver.dbo.Table as unlinkedTable on unlinkedTable.Id = linkedTable.id_categorie
where linkedTable.id_categorie = 1

This work but only one row is updated. So I wrote a stored procedure to update each line but it took too much time.

Can someone explain why my first query didn't work (question1) and how I can reduce the time of my stored procedure (question2)?

I use while loop (count the number of id and update each id).

Thank you in advance.

Kind Regards.

Solution

  • I resolve the problem by checking some option on ODBC Driver in MySQL and reading some forum. I check this box. enter image description here

    This option allows to avoid the errors quoted previously. With this option, i can update multiple values without error on join or other request. Thank you Solarflare and "Another guy" (i lost the name) for correcting me (EDIT A POST). Have nice day both.