0

I have 3 tables which are the dr_table,sales_tb and masterfile_tb. the itemcode is INNER JOIN but my problem is want to update the qty onhand of masterfile_tb based on the column name equal to data of dr_table and sales_tb. Would it be possible?

Here my example code:

    $sql = "update masterfile_tb a join
           (select t.vicma_code, sum(t.qty) as qty
            from ((select vicma_code, qty
                   from handymandr_tb bc inner join
                        masterfile_tb ait
                        on bc.vicma_code = ait.in_code 
                  ) union all

                  (select  vicma_code, (qty * -1) 
                   from sales_tb slp inner join
                        masterfile_tb ait2
                        on slp.vicma_code = ait2.in_code
                  )
                 ) t
            group by vicma_code
           ) i

handymandr_tb and sales_tb data = masterfile_tb column name so i can get the qty onhand of each items and branches. For example: In handymandr_tb or sales_tb 00152 branch there's 1 qty 000000012729 and it will update on masterfile_tb on column name 00152.

           on a.in_code = i.vicma_code
        set (depends on the branch_code equals to column name) = i.qty ";

->Here's my dr_table. the list of all transactions. i want the data of branch_code will equal to mastefile_tb column name so i can get the qty onhand for every branches for every item Here's my dr_table. the list of all transactions. i want the data of branch_code will equal to mastefile_tb column name so i can get the qty onhand for every branches for every item

this is my masterfile_tb where the branch_code data of dr_tb will be equal to masterfile_tb so i can update and get the result of each onhand for every branches and item,

this is my masterfile_tb where the branch_code data of dr_tb will be equal to masterfile_tb so i can update and get the result of each onhand for every branches and item.

codeSeven
  • 479
  • 5
  • 23
  • It's certainly possible, have you tried anything so far? StackOverflow isn't a free code-writing service, and expects you to [**try to solve your own problem first**](http://meta.stackoverflow.com/questions/261592). Please update your question to show what you have already tried, showcasing a **specific** problem you are facing in a [**minimal, complete, and verifiable example**](http://stackoverflow.com/help/mcve). – Obsidian Age Mar 02 '18 at 03:58
  • @ObsidianAge , see my updated post – codeSeven Mar 02 '18 at 05:36

1 Answers1

0

See the example

update ud u inner join sale s on u.id = s.udid set u.assid = s.assid

from How can I do an UPDATE statement with JOIN in SQL?

Abdul Qayyum
  • 1,024
  • 7
  • 15