title: Update duplicate value in MySql with nested select
body: <p>Please help me with my MySql Script, I caught up with an error.
  I have a Table (tbl_products) with the field of p_id and prod_skuid i wanted to update all duplicate prod_skuid into my tbl_products.</p>
  
  <p>Here is my MySql script:</p>
  
  <pre><code>UPDATE tbl_product t1 SET t1.prod_skuid = t.prod_skuid  
  (
  SELECT p_id, prod_skuid from 
  (SELECT p_id,if(@row = prod_skuid, @count, @count := 1),
   CONCAT(prod_skuid, '', @count) as prod_skuid,
   @row := prod_skuid,
   @count := @count + 1
   from tbl_product JOIN (SELECT @count := 0, @row := "") as t
   WHERE prod_skuid IN(SELECT prod_skuid from tbl_product group by prod_skuid having count(prod_skuid) &gt; 1)
  ) as final                          
  ) as t  
  WHERE t1.p_id = t.p_id
  </code></pre>
  
  <p>I managed to display and changed the prod_skuid just using the nested select without the update scrpit, however when i put the update script it says there is an error with my code:</p>
  
  <pre><code>SELECT p_id, prod_skuid from 
  (SELECT p_id,if(@row = prod_skuid, @count, @count := 1),
   CONCAT(prod_skuid, '', @count) as prod_skuid,
   @row := prod_skuid,
   @count := @count + 1
   from tbl_product JOIN (SELECT @count := 0, @row := "") as t
   WHERE prod_skuid IN(SELECT prod_skuid from tbl_product group by prod_skuid having count(prod_skuid) &gt; 1)
  ) as final
  </code></pre>
  
  <p>Error:
  <a href="https://i.stack.imgur.com/rx99k.png" rel="nofollow noreferrer">Mysql Error Message</a></p>