title: PHP MySQL update with multiple WHERE (syntax error)
body: <p>Here is query that works </p>
  
  <pre><code>UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
  WHERE CompanyName = ? OR RegistrationNumber = ?
  </code></pre>
  
  <p>I just want in one query make multiple updates, like </p>
  
  <pre><code>UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
  WHERE CompanyName = ? OR RegistrationNumber = ?
  
  UPDATE 18_6_TransactionPartners SET CompanyName = ?, VatCode = ? 
  WHERE CompanyName = ? OR RegistrationNumber = ?
  </code></pre>
  
  <p>Based on this <a href="http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/" rel="nofollow noreferrer">http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/</a> example </p>
  
  <pre><code>UPDATE mytable
  SET (title='Great Expectations' WHERE id='1'),
  (title='War and Peace' WHERE id='2');
  </code></pre>
  
  <p>Changed query to this</p>
  
  <pre><code>UPDATE 18_6_TransactionPartners 
  SET (CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?), 
  (CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?)
  </code></pre>
  
  <p>But get error <code>Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CompanyName = 'first name', VatCode = '123' WHERE CompanyName = 'first name' OR'</code></p>
  
  <p>As understand, error is after <code>SET</code>.</p>
  
  <p>Removed brackets. For first <code>CompanyName = ?, VatCode = ? WHERE CompanyName = ? OR RegistrationNumber = ?</code> no error, but error is near second <code>CompanyName = ?, VatCode = ?</code></p>
  
  <p>What is correct syntax?</p>
  
  <p>Tried also this <a href="https://stackoverflow.com/a/15344247/2465936">https://stackoverflow.com/a/15344247/2465936</a></p>
  
  <pre><code>UPDATE 18_6_TransactionPartners 
  SET value = CASE 
  WHEN CompanyName = ? OR RegistrationNumber = ? THEN CompanyName = ?, VatCode = ? , 
  WHEN CompanyName = ? OR RegistrationNumber = ? THEN CompanyName = ?, VatCode = ? 
  ELSE CompanyName, RegistrationNumber END
  </code></pre>
  
  <p>But get error <code>SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' VatCode = '123' , WHEN CompanyName = 'second name' OR RegistrationNumber = 'ttt'</code></p>
  
  <p><strong>Update</strong></p>
  
  <p>Experimented and works such code: </p>
  
  <pre><code>UPDATE 18_6_TransactionPartners SET RegistrationNumber = (
  CASE
  WHEN CompanyName = 1 OR VatCode = 1 THEN 1
  WHEN CompanyName = 2 OR VatCode = 2 THEN 2
  WHEN CompanyName = 3 OR VatCode = 'three' THEN 'three'
  ELSE RegistrationNumber
  END)
  WHERE CompanyName IN ('1', '2', '3') OR VatCode IN ( 1, 2, 'three' ) //aim is to search table only where `WHERE` is true
  </code></pre>
  
  <p>So for each <code>UPDATE</code> only one <code>SET RegistrationNumber = CASE</code> but can set multiple conditions <code>WHEN CompanyName = 1 OR VatCode = 1</code>.</p>
  
  <p>Next either to try to combine multiple <code>UPDATE 18_6_TransactionPartners SET RegistrationNumber = CASE</code> <code>UPDATE 18_6_TransactionPartners SET RegistrationNumberNEXT = CASE</code></p>
  
  <p>or need to create multiple queries.... Seems need to try this example <a href="http://code.openark.org/blog/mysql/multi-condition-update-query" rel="nofollow noreferrer">http://code.openark.org/blog/mysql/multi-condition-update-query</a></p>
  
  <pre><code>UPDATE
   film
  SET
   rental_duration=IF(rating = 'G', rental_duration+1, rental_duration),
   rental_rate=IF(length &lt; 90, rental_rate-0.5, rental_rate)
  WHERE
   rating = 'G'
   OR length &lt; 90
  </code></pre>