如何根据旧表中的3列更新和新建表 - How can I update and new table based on 3 columns from an old table?

How can I update and new table based on 3 columns from an old table?

本文关键字：3列更新新建何根 | 更新日期: 2023-09-27

我有两个表：

带列的new_product：

id_product, 
id_category, 
id_combination, 
id_feature, 
whole_sale_price, 
retail_price,

带列的old_product：

id_product, 
id_category, 
id_combination, 
id_feature, 
whole_sale_price, 
retail_price,

如何使用old_product中whole_sale_price和retail_price列的值更新new_product上whole_sale_price和retail_price列的数据（使用关键字id_category、id_combination和id_feature）？

一般来说，您可以按如下方式编写：

UPDATE
  table1 AS target,
  (SELECT column1, column2 FROM table2) AS source
SET
  target.column3 = source.column1
WHERE
  target.column4 = source.column2

如果要更新whole_sale_price和retail_price，可以使用以下查询：

UPDATE new_product as newP
JOIN old_product as oldP
ON newP.id_category = oldP.id_category AND
   newP.id_combination = oldP.id_combination AND
   newP.id_feature = oldP.id_feature
SET newP.whole_sale_price = oldP.whole_sale_price,
newP.retail_price = oldP.retail_price