phpmyadmin Question posted in
The official documentation can be found here.

MySQL update cell from one table to anoher – Phpmyadmin

I need to take value ecotax from table ps_product for id_product and update value ecotax on same id_product in table ps_product_shop.

Make this command:

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

but get:

#1048 – Column ‘ecotax’ cannot be null

values are 0.0000 or 0.1580 and so on. As well tried IN NOT NULL in SELECT but didnt help. Command is processed by phpMyAdmin.

Any idea please?

Tags:

2

Answers


  1. 0

    You can follow two ways:

    The first:

    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)

    The second:

    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
    Login or Signup to reply.
  2. 0

    The only possible reason I can conjure up to explain this is that you’re not qualifying ecotax inside the subquery with a proper alias. Try doing that:

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

    But I actually recommending taking advantage of MySQL’s update join syntax here (assuming you’re actually using MySQL):

    UPDATE ps_product_shop ps
LEFT JOIN ps_product p
    ON p.id_product = ps.id_product
SET
    ecotax = ps.ecotax;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search