How I Fix MySQL Error 1442 When Update a Parent Product Price from a Trigger

Recently, I was working on a project where I had to update a product’s price, and if that product had a parent item, I needed the parent’s price to also update under certain conditions. It sounded simple at first use a MySQL trigger to detect the price change and then update the parent. But the moment I ran the update, I got hit with this frustrating error:

Error Code

SQL Error (1442): Can't update table 'product' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

At first, I didn’t understand why it wouldn’t let me update. I double-checked my code, it seemed logically sound. Here’s the exact trigger I had written:

My Code

  SET @parentid := (SELECT parent_id FROM product WHERE product_id = NEW.product_id);
  IF (OLD.price <> NEW.price) THEN
    UPDATE product SET price = NEW.price 
    WHERE product_id = @parentid AND price > NEW.price;
  END IF;
END

The logic was:

• Check if the price was changed
• If yes, grab the parent ID
• If the parent’s current price was more than the child’s new price, then lower the parent’s price too

This should work, right? But nope. MySQL threw the 1442 error.

What’s Really Going On?

The root of the issue lies in how MySQL triggers behave.
When you trigger an event (like UPDATE) on a table, you’re not allowed to modify that same table from inside the trigger.

Even if it’s a different row, MySQL prevents it to avoid potential recursion, infinite loops, and deadlocks.

So in my case:

• I updated a row in product
• The trigger fired on product
• Inside the trigger, I tried to update another row in product (the parent)
• MySQL immediately stopped me with error 1442

This is by design, and there’s no MySQL setting or flag to bypass it.

Using a Stored Procedure

Since I couldn’t perform that update inside a trigger, I switched to a better approach a stored procedure. Then I would manually call this procedure whenever I needed to update a product’s price.

Create a Price History Table

Before anything else, I wanted to keep a history of all price changes. So I created a new table:

TABLE product_price (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  price DECIMAL(10,2),
  date_added DATETIME
);

Create the Stored Procedure

I wrote a procedure that would:

• Fetch the current price and parent ID
• Compare the old and new prices
• Save the price change in a history table
• Update both the current product and its parent (if necessary)

DELIMITER $$

CREATE PROCEDURE update_product_price(IN p_product_id INT, IN p_new_price DECIMAL(10,2))
BEGIN
  DECLARE v_old_price DECIMAL(10,2);
  DECLARE v_parent_id INT;

  -- Get current price and parent ID
  SELECT price, parent_id INTO v_old_price, v_parent_id
  FROM product
  WHERE product_id = p_product_id;

  -- Only continue if price has changed
  IF v_old_price <> p_new_price THEN
    -- Save history
    INSERT INTO product_price (product_id, price, date_added)
    VALUES (p_product_id, p_new_price, NOW());

    -- Update product
    UPDATE product SET price = p_new_price
    WHERE product_id = p_product_id;

    -- Update parent if needed
    IF v_parent_id IS NOT NULL AND v_parent_id > 0 THEN
      UPDATE product
      SET price = p_new_price
      WHERE product_id = v_parent_id AND price > p_new_price;
    END IF;
  END IF;
END$$

DELIMITER ;

Use the Procedure

Now, instead of doing a direct update like this:

product SET price = 200 WHERE product_id = 5;

I now run:

update_product_price(5, 200);

This handles everything: history logging, parent check, conditional update and no more error 1442!

Practice Functionality I Added

To make this solution more robust, I added a few extra features to the stored procedure:

• Price Change Logs

INTO price_change_log (product_id, old_price, new_price, changed_on)
VALUES (p_product_id, v_old_price, p_new_price, NOW());

• Check for large price drops

p_new_price < (v_old_price * 0.5) THEN
  -- maybe raise a warning or send alert
END IF;

• Support multiple parent levels
  I’m still working on recursive logic for multi-level parents, but even basic one-level support has improved the workflow a lot.

Final Thoughts

If you’re running into MySQL Error 1442, stop trying to hack around it with triggers. It’s not a bug it’s a safeguard. MySQL intentionally prevents you from updating the same table inside a trigger to avoid dangerous recursive behavior.