title: how to fix columns with '?' characters in MySQL
body: <p>I have a table with lots of data incorrectly saved with '<code>?</code>' symbols instead of the correct ones. </p>
  
  <p>Most (if not all) of them where due to encoding differences, so instead of saving <code>á,é,í,ó,ú and ñ</code> (and the uppercase versions of each) they all where stored like '<code>?</code>'</p>
  
  <p>for instance, i have the left ones instead of the right ones:</p>
  
  <pre><code>Se?ora &lt; Señora
  Espa?na &lt; España
  Jos? &lt; José
  Del r?o &lt; Del río
  </code></pre>
  
  <p>and so on...</p>
  
  <p>Obviously, I couldn't know directly, if a <code>?</code> represents any of <code>á,é,í,ó,ú or ñ</code> , <strong>BUT</strong> i have another table, which has the correct values (with accented letters and ñ) for most fields in the columns i need.</p>
  
  <p>My question is, how could I build a query (or a script in PHP), that will update columns in table A (the wrong one) so the values are replaced with the correct ones from B.</p>
  
  <p>Note that each "pair" of columns (from A and B) are always the same lenght, so the only difference is that in A they have '?' symbols, and in B they have the correct letter. With that in mind, I imagine I could build some kind of query using likes, and making a <code>like</code> each time, replacing any of <code>á,é,í,ó,ú and ñ</code> with <code>_</code>s in the like command. </p>
  
  <p>e.g.:</p>
  
  <p>if correct column from B is <code>'de la peña'</code>, the <code>like</code> would be <code>'de la pe_a'</code> so it would match <code>'de la pe?a'</code> in A, and then i just need to replace the value in A with the one in B, effectively replacing <code>'de la pe?a'</code> with <code>'de la peña'</code>.</p>
  
  <p>the problem is I have no idea of how to make such a query / script.</p>
  
  <p>any ideas?</p>
  
  <p>Note 1, I know a good idea would be to simply replace A with B entirely, BUT the problem is that B has much fewer records than A, so i would only be able to fix as many wrong records in A as they have a matching pair in B.</p>
  
  <p>Note 2. I already know how to correctly save <strong>new</strong> data from now on (it was a charset problem), and new records are now saved correctly, but i need to fix the wrong <strong>OLD</strong> data that is already there...</p>