Replace a product code with the product id from another table in sql

Question

the previous developer has done a wrong practice in a sql database with phpmyadmin, which is creating a problem of duplicate contents.

Correct way:

table 1
ORDERS
id_order
id_product

table 2
PRODUCTS
id_product
internal_product_code

———

Wrong way:

table 1
ORDERS
id_order
internal_product_code

table 2
PRODUCTS
id_product
internal_product_code

Unfortunately, the internal_product_code is NOT always unique**, while id_product and id_order are always unique. So today my client has get the first duplicate data content, because there's two products with different id_product but same internal_product_code.

** the same internal_product_code due to a very similar specs (this is another bad practice)**

Is there any way to replace table1.internal_product_code with table2.id_product in order to follow the correct way? I need to do it in the database (after a strong backup).

Thanks to all

Try to write the query as it [written here](https://stackoverflow.com/a/5730455/7663972). We'll help if you have problems with it. — camelsWriteInCamelCase, Nov 21 '17 at 22:55

score 0 · Answer 1 · answered Nov 21 '17 at 23:19

0

UPDATE orders SET products = (SELECT orders.id_order FROM products WHERE orders.id_product = products.internal_product_code LIMIT 1)

answered Nov 21 '17 at 23:19

R M

297
1
3
14

Replace a product code with the product id from another table in sql

1 Answers1