title: Update query on linked MySQL table from SQL Server
body: <p>I have a MS SQL Server with a linked MySQL server. I need to partially synchronize a table between the two servers. This is done in three steps and based on a condition:</p>
  
  <ol>
  <li><p>Delete all rows from the MySQL table that do not satisfy the condition</p></li>
  <li><p>Insert all new rows in the MySQL table that satisfy the condition</p></li>
  <li><p>Update all rows in the MySQL server that satisfy the condition and have different data between MySQL and SQL Server</p></li>
  </ol>
  
  <p>Steps 1 and 2 always run without a problem. But step 3 won't run if there is anything to update. The query fails with the following exception: <em>The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].</em></p>
  
  <p>This is the query that is executed:</p>
  
  <pre><code>update mysqlserver...subscribers
  set Firstname = Voornaam, 
    Middlename = Tussenvoegsel, 
    Surname = Achternaam, 
    email = e-mail 
  from mysqlserver...subscribers as b, tblkandidaat 
  where (b.kandidaatid = tblkandidaat.kandidaatid) and
    (tblkandidaat.kandidaatid in (
      select subsc.kandidaatid
      from mysqlserver...subscribers subsc inner join tblKandidaat 
        on (subsc.kandidaatid=tblKandidaat.kandidaatid) 
      where (subsc.list=1) and
        ((subsc.firstname COLLATE Latin1_General_CI_AI &lt;&gt; Voornaam 
        or (subsc.middlename COLLATE Latin1_General_CI_AI &lt;&gt; Tussenvoegsel) 
        or (subsc.surname COLLATE Latin1_General_CI_AI &lt;&gt; tblKandidaat.Achternaam) 
        or (subsc.email COLLATE Latin1_General_CI_AI &lt;&gt; tblKandidaat.e-mail))
    ));
  </code></pre>
  
  <p>Anybody has an idea about how to prevent this?</p>