mysql - INSERT ... SELECT Syntax issue when value(s) from two or more different query -

i have table name shop_balance. has 3 columns
(shop_balance_id(int,pk),
shop_balance(double),
balance_date(date)).


shop_balance(double) column use 2 sub query.
1.get last shop balance amount row shop_balance column in shop_balance table.
2.get purchase amount after 1 purchase product(s).
, subtract them , current shop balance

my query here

insert shop_balance  select null, (     (select shop_balance shop_balance             shop_balance_id=(select max(shop_balance_id) shop_balance)     )          -     (     select          sum(pr_pur_cost_price*quantity) net product_purchase_item     left join          product_purchases p     on          p.product_purchase_item_id=i.product_purchase_item_id              p.insert_operation=$id     group          p.insert_operation     ) ),curdate(); 

it clear 2 sub query different condition , no direct relation them. above insert query work well. idea use many sub query without insert ... select syntax insert 1 value? if not, how can convert insert ... select syntax?

you have calculation way calculation. guessing trigger might better meet needs, doing logic in insert fine.

the following simplifies query. eliminates double subquery on shop_balance, changes left join inner join (you have condition on second table), , eliminates group by second subquery:

insert shop_balance      select null,            ((select shop_balance              shop_balance               order shop_balance_id desc              limit 1             ) -             (select sum(pr_pur_cost_price*quantity) net              product_purchase_item join                    product_purchases p                   on p.product_purchase_item_id=i.product_purchase_item_id              p.insert_operation=$id            )           ), curdate(); 

you should list columns in insert clause, , eliminate first null (it set null default). final curdate() suggests might want automatic column store insertion time well.