MySQL从一个表中更新单元格到指定单元格

Question

我需要从表ps_product获取id_product的价值生态税，并在表ps_product_shop中的同一id_product上更新价值生态税。

发出以下命令：

UPDATE `ps_product_shop` 
SET `ecotax` = (SELECT `ecotax` FROM `ps_product`
                WHERE ps_product.`id_product` = 
                      ps_product_shop.`id_product`);

但是得到：

#1048 -列“生态税”不能为空

值为0.0000或0.1580，依此类推。在SELECT中也尝试了，但是没有帮助。命令由phpMyAdmin处理。

有什么想法吗？

Answer 1

你可以遵循两种方式：

第一项：

UPDATE ps_product_shop 
SET ecotax = 
    (SELECT ps_product.ecotax FROM ps_product WHERE ps_product.id_product = 
    ps_product_shop.id_product)
WHERE EXISTS(SELECT 1 FROM ps_product WHERE ps_product.id_product = 
    ps_product_shop.id_product)

第二部分：

UPDATE ps_product_shop 
SET ecotax = 
    (SELECT ps_product.ecotax FROM ps_product WHERE ps_product.id_product = 
    ps_product_shop.id_product)
WHERE (SELECT ps_product.ecotax FROM ps_product WHERE ps_product.id_product = 
    ps_product_shop.id_product) IS NOT NULL

Answer 2

我可以解释这一点的唯一可能原因是，您没有使用适当的别名对子查询中的ecotax进行限定。试着这样做：

UPDATE ps_product_shop ps
SET ecotax = (SELECT p.ecotax FROM ps_product p
              WHERE p.id_product = ps.id_product);

但实际上，我建议在这里利用MySQL的update语法(假设您实际使用的是MySQL)：

UPDATE ps_product_shop ps
LEFT JOIN ps_product p
    ON p.id_product = ps.id_product
SET
    ecotax = ps.ecotax;