CODEWITHSUNDEEP
mysqlsqltriggerssql-updatesql-insert
techedifice
techedifice

Reputation: 163

MySQL trigger: Using value from another table

I am using MYSQL 8.0.18.

I have three tables: trade, portfolio and closed_trades.

I am trying to create a trigger after update on trade table which will insert values on closed_trades tables. in this insert task average_price value from portfolio table is also needed to be inserted in the closed trade table. but I cannot get that value

DELIMITER $$
CREATE TRIGGER closed_trade_update
    after UPDATE ON trade for each row
    BEGIN
        IF NEW.action = 'sell'
            THEN
                INSERT INTO closed_trades
                SET ticker = NEW.ticker,
                    quantity = NEW.quantity,
                    sell_price = NEW.real_price,
                    avg_cost = portfolio.average_price;

            END IF;
    End;
$$
DELIMITER ;

I get this error message "Unknown column 'portfolio.average_price' in 'field list'"

details of my tables

DESC trade;

+-------------+---------------------+------+-----+-------------------+-------------------+
| Field       | Type                | Null | Key | Default           | Extra             |
+-------------+---------------------+------+-----+-------------------+-------------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment    |
| ticker      | varchar(25)         | NO   | MUL | NULL              |                   |
| action_date | datetime            | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| action      | enum('buy','sell')  | NO   |     | NULL              |                   |
| basic_price | decimal(10,2)       | NO   |     | NULL              |                   |
| real_price  | decimal(10,2)       | YES  |     | NULL              |                   |
| quantity    | decimal(10,0)       | YES  |     | NULL              |                   |
| total       | decimal(10,0)       | YES  |     | NULL              |                   |
+-------------+---------------------+------+-----+-------------------+-------------------+

DESC portfolio;

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| ticker        | varchar(25)   | NO   | PRI | NULL    |       |
| quantity      | decimal(10,0) | YES  |     | NULL    |       |
| average_price | decimal(10,2) | YES  |     | NULL    |       |
| value         | decimal(10,2) | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

DESC closed_trades;
+-----------------+---------------------+------+-----+------------------------------------------------------------+-------------------+
| Field           | Type                | Null | Key | Default                                                    | Extra             |
+-----------------+---------------------+------+-----+------------------------------------------------------------+-------------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL                                                       | auto_increment    |
| ticker          | varchar(25)         | NO   | MUL | NULL                                                       |                   |
| action_date     | datetime            | YES  |     | CURRENT_TIMESTAMP                                          | DEFAULT_GENERATED |
| sell_price      | decimal(10,2)       | YES  |     | NULL                                                       |                   |
| avg_cost        | decimal(10,2)       | NO   |     | NULL                                                       |                   |
| quantity        | decimal(10,0)       | YES  |     | NULL                                                       |                   |
| total_received  | decimal(10,2)       | YES  |     | (`sell_price` * `quantity`)                                | DEFAULT_GENERATED |
| total_cost      | decimal(10,2)       | YES  |     | (`avg_cost` * `quantity`)                                  | DEFAULT_GENERATED |
| capital_gain    | decimal(10,2)       | YES  |     | (`total_received` - `total_cost`)                          | DEFAULT_GENERATED |
| gain_percentage | decimal(10,3)       | YES  |     | (((`total_received` - `total_cost`) / `total_cost`) * 100) | DEFAULT_GENERATED |
+-----------------+---------------------+------+-----+------------------------------------------------------------+-------------------+

Please Help me to complete my project. I will be very grateful to you.

Upvotes: 1

Views: 1725

Answers (1)

GMB
GMB

Reputation: 222672

I think you want insert ... select :

insert into closed_trades (ticket, quantity, sell_price, avg_cost)
select new.ticker, new.quantity, new.real_price, p.average_price
from portfolio p
where p.ticker = new.ticker

If there is a possibility that the ticker of the updated row might not exists in portfolio, and you still want a row inserted in closed_trades, with a null value in avg_cost, you can use a subquery instead:

insert into closed_trades (ticket, quantity, sell_price, avg_cost)
values (
    new.ticker, 
    new.quantity, 
    new.real_price, 
    (select p.average_price from portfolio p where p.ticker = new.ticker)
)

Upvotes: 1

Related Questions