fixing duplicate fields in mysql table with update statement

Question

I have inherited a table with a field "sku" with should be unique, but thanks to a failing sku-generating method is now littered with dozens of duplicates all around.

I need to quickly fix these duplicates (other parts of the application are failing when encountering these duplicate records) by running an update and appending the record ID to the SKU (which is a valid solution for the time being for this application).

I'm trying to run:

UPDATE
 main_product_table
SET sku = CONCAT(sku, '-', CAST(product_id as CHAR) )

WHERE sku IN (
  SELECT sku FROM main_product_table
  GROUP BY sku
  HAVING COUNT(*) > 1
);

But I receive:

You can't specify target table 'main_product_table' for update in FROM clause

Is there a way to accomplish the same? Is mysql complaining about me having main_product_table both in the update and in the subquery to get the duplicates?

Thanks!

I guess it is a problem with using the same table name twice. Try using unique aliases for the tables. — Sarath Chandra
– Sarath Chandra, Commented Jan 20, 2016 at 8:40
No, that's not it. Tried aliasing both tables, same result. (For the record, the error references the first alias, not the sencond) — yivi
– yivi, Commented Jan 20, 2016 at 8:43

Abhishek · Accepted Answer · 2016-01-20 08:51:06Z

2

Try this:

UPDATE
 main_product_table
SET sku = CONCAT(sku, '-', CAST(product_id as CHAR) )

WHERE sku IN (
select * from  ( SELECT sku FROM main_product_table
  GROUP BY sku
  HAVING COUNT(*) > 1) as p
);

Added table alias in inner query.

answered Jan 20, 2016 at 8:51

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

fixing duplicate fields in mysql table with update statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related