-1

I have two table one tblstationerystock (stationery_name,stationery_code, balance) and other tblstationerytranscation(stationery_code,trsntype,quantity).Trsntype column is transction type consists of two type of value (received or issued)

I want to calculate the value of balance in first table based on any transcation for stationery in second table.my table looks like this

Clickk here for table

I dont have much knowledge about sql .Is it possiblePlease guide me to achieve this .

GMB
  • 216,147
  • 25
  • 84
  • 135
hellow
  • 1
  • 4
  • this already exists in dba https://dba.stackexchange.com/questions/278612/calculating-the-record-value – nbk Oct 24 '20 at 17:11
  • Please refer to https://meta.stackoverflow.com/questions/262292/how-do-i-handle-duplicates-when-they-are-asked-on-other-se-sites – Abra Oct 24 '20 at 17:15

1 Answers1

0

One option is an update/join query:

update tblstationerystock s
inner join (
    select stationery_code, 
        sum(case when trsntype = 'RECEIVED' then quantity else -quantity end) balance
    from tblstationerytranscation
    group by stationery_code
) t on t.stationery_code = s.stationery_code
set s.balance = t.balance

From design perspective, storing such information is not a good thing, because it is hard to keep it up to date; you might soon find yourself in need for a trigger, that automatically updates the master table whenever a transaction is inserted, modified or deleted in the other table. This is derived information, that can be computed on the fly whenever needed. For this, you can, for example, create a view:

create view w_tblstationerystock as
select s.stationery_name, s.stationery_code 
    (
        select sum(case when trsntype = 'RECEIVED' then quantityu else -quantity end) balance
        from tblstationerytranscation t
        where t.stationery_code = s.stationery_code
    ) as balance
from tblstationerystock s

You can then run your queries directly against the view, which provides an always up-to-date perspective at your data.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • where should i run this query on first table or second table – hellow Oct 24 '20 at 17:14
  • @hellow: this query updates the `balance` in the `tblstationerystock` table from the `tblstationerytranscation` table - which is how I understood your question. But see my updated answer for additional thoughts on what you are trying to accomplish. – GMB Oct 24 '20 at 17:18
  • update tblstationerystock inner join ( select sum(case when txntype = 'RECEIVED' then quantity else -quantity end) balance from tblstationerytranscation group by stationery_code ) on tblstationerytranscation.stationery_code = tblstationerystock.stationery_code set tblstationerystock.balance = tblstationerytranscation.balance – hellow Oct 24 '20 at 17:26
  • i used above query but it shows error --You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on tblstationerytranscation.stationery_code = tblstationerystock.stationery_code' – hellow Oct 24 '20 at 17:27
  • @hellow: this is not the query I provided you with. Please run the query exactly as given, and report if it works or not. – GMB Oct 24 '20 at 17:28
  • Unknown column 't.stationery_code' in 'on clause' this is thee error – hellow Oct 24 '20 at 17:30
  • @hellow: ah yes, that is correct. Fixed. – GMB Oct 24 '20 at 17:32
  • It is working can you tell me how to run this query automaticallyy whenever any update in the second table – hellow Oct 24 '20 at 17:36
  • @hellow: this is what the rest of my answer discusses. I would not recommend storing that information. Keeping it up to date is tedious. You can use the view instead, as suggested. – GMB Oct 24 '20 at 17:37