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

Update recently updated row by id – Phpmyadmin

In my prestashop database I tried to update the default product attribute when there is 0 quantity for that one. I check if quantity is 0 and then set default_on to null. Then I try to set another attribute default_on with the same id_product only if quantity is bigger than 0.

I have 3 problems:

  1. How to check that there is only 1 result for specific id_product? If there is only 1 i don’t want to update default_on.
  2. How to check that all results for specific id_product has quantity = 0? I don’t want to update default_on in this case too.
  3. How to make this for all table? Loop?
START TRANSACTION;
SET @update_id := 0;
UPDATE ps_product_attribute SET default_on = NULL, id_product = (SELECT @update_id := id_product)
WHERE `default_on` = 1 AND `quantity` = 0
LIMIT 1;

UPDATE ps_product_attribute SET default_on = 1
WHERE `id_product` = @update_id AND `quantity` > 0 AND `default_on` IS NULL
LIMIT 1;
COMMIT;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Now i try different way: 1. Set all default_on to null 2. Set default_on to 1 on first id_product_attribute from id_product group which has quantity > 0

    Problem is: what to do with all the rest product where all attributes has 0 quantity?

    UPDATE ps_product_attribute AS t1
JOIN (
    SELECT MIN(id_product_attribute) MinID
      FROM ps_product_attribute
    WHERE quantity > 0
      GROUP BY id_product
      HAVING COUNT(*) > 1
) AS t2 ON t1.id_product_attribute = t2.MinID
SET default_on = 1

  2. 0

    Use a self-join that gets all the products with more than one result.

    UPDATE ps_product_attribute AS t1
JOIN (
    SELECT id_product
    FROM ps_product_attribute
    GROUP BY id_product
    HAVING COUNT(*) > 1
) AS t2 ON t1.id_product = t2.id_product
SET default_on = CASE
    WHEN default_on = 1 AND quantity = 0 THEN NULL
    WHEN quantity > 0 AND default_on IS NULL THEN 1
    ELSE default_on -- leave the rest unchanged
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search