我有两个表:
表PRODUCTS
| ProductID | ProductPrice | ProductSupplier |
+-----------+---------------+-----------------+
| 1 | 25 | CompanyA |
| 2 | 35 | CompanyB |
| 3 | 12 | CompanyC |
表供应商
SupplierID | SupplierName | 1 | CompanyA | 2
---|---|
为CompanyB | |
3 | CompanyC |
使用update join:
UPDATE PRODUCTS p
INNER JOIN SUPPLIERS s
ON s.SupplierName = p.ProductSupplier
SET p.SupplierID = s.SupplierID;
请注意,您正朝着更加规范化的方向前进,这是一件好事。假设您打算保留SUPPLIER
表,那么PRODUCTS
表中的ProductSupplier
列现在是冗余的,可以删除:
ALTER TABLE PRODUCTS DROP COLUMN ProductSupplier;