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.


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more