mysqlsqlstored-procedurestriggersmysql-error-1054

How to make a SQL UPDATE procedure use more than 1 table in the calculation? Doing so gives me error 1054


After I defined a trigger and its procedure, to diminish the amount of books on stock by an amount specified on a separate table (both being variable), it gives me error 1054, of an unknown column in the procedure. The code that defines the trigger and procedure is the following:

CREATE DEFINER=`root`@`localhost` TRIGGER reduce_available
AFTER INSERT ON book_customer FOR EACH ROW
UPDATE stock SET stock.current_amount = stock.current_amount-book_customer.amount;

Usually to process the type of interactions that would warrant this type of trigger instead I generate an entirely new table that records the purchases and sellings, with aditional useful data. However, in this specific case I don't have such a table, and would like to know how to work around it.


Some clarification:

Additional information:


Edits:

  1. Solved! Rookie mistake; I used an uncomplete sintaxis without a "where" clause.
    The reason I didn't find it while searching was that I didn't know there could be more than 1 sintaxis in the first place.
  2. I forgot to say many thanks to Linoff!
  3. Improving readability of the question.

Solution

  • Do you just want to refer to new? If so, I assume you only want to update one row in stock rather than all of them. You need a where clause

    CREATE DEFINER=`root`@`localhost` TRIGGER reduce_available
    AFTER INSERT ON book_customer FOR EACH ROW
    BEGIN
        UPDATE stock s
            SET s.current_amount = s.current_amount - new.amount
            WHERE s.book_id = new.book_id;
    END;