title: mysql update field in table from other table with the link in a third table
body: <p>Migrating products from one instance of prestashop to another, I've ran into an issue of changed id's for images (id_image) but luckily same product id's (id_product).</p>
  
  <p>Now that I have migrated the majority of the data, only the field 'cover' in the table dstshop_image_shop is missing.</p>
  
  <p>Basically I want the table dstshop_image_shop to contain</p>
  
  <pre><code>(100, 1, 0),
  (150, 1, 1),
  (180, 1, 1);
  </code></pre>
  
  <p>Tables and some sample data are as below:</p>
  
  <pre><code>-- Tables for Source Shop
  CREATE TABLE srcshop_image (`id_image` int, `id_product` int, `position`int);
  CREATE TABLE srcshop_image_shop (`id_image` int, `id_shop` int, `cover` int);
  
  -- Tables for Destination Shop
  CREATE TABLE dstshop_image (`id_image` int, `id_product` int, `position`int);
  CREATE TABLE dstshop_image_shop (`id_image` int, `id_shop` int, `cover` int);
  
  -- Insert data
  INSERT INTO srcshop_image (`id_image`, `id_product`, `position`)
  VALUES 
  (1, 5, 1),
  (2, 5, 2),
  (3, 6, 1);
  
  INSERT INTO srcshop_image_shop (`id_image`, `id_shop`, `cover`)
  VALUES
  (1, 1, 0),
  (1, 2, 0),
  (2, 1, 1),
  (2, 2, 1),
  (3, 1, 1),
  (3, 2, 1);
  
  INSERT INTO dstshop_image (`id_image`, `id_product`, `position`)
  VALUES
  (100, 5, 1),
  (150, 5, 2),
  (180, 6, 1);
  
  INSERT INTO dstshop_image_shop (`id_image`, `id_shop`, `cover`)
  VALUES
  (100, 1, -1),
  (150, 1, -1),
  (180, 1, -1);
  </code></pre>
  
  <p>I think that my sql skills (or lack of) has made me hit the wall with this one...</p>
  
  <p>BR
  Mads</p>