i'm trying to update mySQL table via linked server MSSQL. My query looks like this:
update openquery(CASTOMYSQL, 'select * from results_dwh_d where id_family = 0') set id_region = 0
When i execute this query and values in column id_region are not equal 0 everything works fine but when id_region are equal 0 a got error.
Row cannot be located for updating. Some values may have been changed since it was last read.
I don't particularly care to use OPENQUERY when performing updates/inserts/deletes against a linked server. Instead I opt to use the EXECUTE AT command.
For example:
EXECUTE('UPDATE results_dwh_d SET id_region = 0 WHERE id_family = 0') AT CASTOMYSQL
The reason I avoid OPENQUERY is because of the idiosyncrasies in the RDBMS platforms regarding ANSI-SQL. Update statements on MSSQL don't and won't always match the syntax requirements for other RDBMS platforms you may be performing the update/insert/delete against. I'm not all that familiar with MySQL, but the query translation OPENQUERY is performing behind the scenes may be causing your issues, and using EXECUTE AS would ensure no syntax tranformation will take place.
Hope that helps,
John
you can use this code,
UPDATE OPENQUERY(lin_server_name,
'select id_region FROM database_name.table_name WHERE id_family = 0'
)
SET id_region = 0
--where id_family = 0;
