Search code examples
mysqlsqlselectinsertsql-update

SQL insert and update data into column based on other Table column

I have two tables:
table PRODUCTS

| ProductID | ProductPrice  | ProductSupplier |
+-----------+---------------+-----------------+
| 1         |    25         | CompanyA        |
| 2         |    35         | CompanyB        |
| 3         |    12         | CompanyC        |

table SUPPLIERS

SupplierID SupplierName
1 CompanyA
2 CompanyB
3 CompanyC

How to insert new column SupplierID into table Products, based on values from table Suppliers but with corresponding values from column ProductSupplier? example of new desired output: Table PRODUCTS

    | ProductID | ProductPrice  | ProductSupplier | SupplierID |
    +-----------+---------------+-----------------+------------+
    | 1         |    25         | CompanyA        | ID value from table Suppliers |
    | 2         |    35         | CompanyB        | ID value from table Suppliers |
    | 3         |    12         | CompanyC        | ID value from table Suppliers |
Solution

  • Use an update join:

    UPDATE PRODUCTS p
INNER JOIN SUPPLIERS s
    ON s.SupplierName = p.ProductSupplier
SET p.SupplierID = s.SupplierID;

    Note that you are moving in the direction of more normalization, which is a good thing. Assuming you are intending to keep the SUPPLIER table, then the ProductSupplier column in the PRODUCTS table is now redundant and can probably be dropped:

    ALTER TABLE PRODUCTS DROP COLUMN ProductSupplier;