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

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 |

CodePudding user response：

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;