PHP/MYSQL - Update subquery select with union

Question

I got my query here which used to display the On-Hand qty using union so i can get the result. allinvty3 is my master file which i want to update the qty of my items based on the result of my union. would that be possible to update to my allinvty3 based on variable output $total .

 **My table :**


     |allinvty3|(masterfile)
      ----------------
      |in_code       |
      |ecr_desc      |
      |pric_cash  
      |qty           |
      |ite_desc      |
      ---------------

|barcode, branchtobranch,adjustment table(all tables have same fieldnames) |
      ----------------
      |itemcode      |
      |qty           |
      |status        |
      ---------------

This is my sample output for my Union all and i want to get the data and able to update my quantity in allinvty3 which is my masterfile

$sql = "UPDATE allinvty3 as A, (
    select 
        ite_desc,ecr_desc, pric_cash, t.itemcode as itemcode ,sum(t.qty) as qty
    from ( 
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from barcode as bc inner JOIN allinvty3 as ait on bc.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from branchtobranch_tb as bb inner JOIN allinvty3 as ait on bb.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,    pric_cash, itemcode,qty from adjustment_tb as adt inner JOIN allinvty3 as ait1 on adt.itemcode = ait1.in_code where adt.status='APPROVED'
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from stockreturn_tb as sb inner JOIN allinvty3 as ait on sb.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from notinclude_tb as nt inner JOIN allinvty3 as ait on nt.itemcode = ait.in_code where nt.status='COMPLETE'
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from purchase_tb as pt inner JOIN allinvty3 as ait on pt.itemcode = ait.in_code 
        union all
        select ite_desc,ecr_desc,   pric_cash, itemcode,(qty * -1) from soldout_dm as slp inner JOIN allinvty3 as ait2 on slp.itemcode = ait2.in_code
    ) as t
  ) as  UD
SET
    A.sa_onhand = UD.qty
WHERE
    A.in_code = UD.itemcode";



$result = $conn->query($sql);

Error when i update just like the suggestion below, though all of my fields are correct

Notice: Trying to get property of non-object

Please share your tables structure and sample data. – Te Ko Oct 10 '16 at 20:24 — Te Ko, Oct 10 '16 at 20:24
@TeKo, i just shared my data. – codeSeven Oct 11 '16 at 02:27 — codeSeven, Oct 11 '16 at 02:27

score 1 · Answer 1 · edited May 23 '17 at 12:13

I think You can use do this like this:

UPDATE allinvty3 A, (
    select 
        ite_desc,ecr_desc, pric_cash, t.itemcode  as itemcode ,sum(t.qty) as qty
    from ( 
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from barcode as bc inner JOIN allinvty3 as ait on bc.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from branchtobranch_tb as bb inner JOIN allinvty3 as ait on bb.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,    pric_cash, itemcode,qty from adjustment_tb as adt inner JOIN allinvty3 as ait1 on adt.itemcode = ait1.in_code where adt.status='APPROVED'
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from stockreturn_tb as sb inner JOIN allinvty3 as ait on sb.itemcode = ait.in_code
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from notinclude_tb as nt inner JOIN allinvty3 as ait on nt.itemcode = ait.in_code where nt.status='COMPLETE'
        union all
        select ite_desc,ecr_desc,  pric_cash,   itemcode,qty  from purchase_tb as pt inner JOIN allinvty3 as ait on pt.itemcode = ait.in_code 
        union all
        select ite_desc,ecr_desc,   pric_cash, itemcode,(qty * -1) from soldout_dm as slp inner JOIN allinvty3 as ait2 on slp.itemcode = ait2.in_code
    )
    group by itemcode order by ecr_desc  ASC
) UD 
SET
    A.qty = UD.qty
WHERE
    A.itemcode = UD.itemcode

More abut UPDATES from SELECT in MySQL here or here.

i'll try. thanks for the help, i believe qyu is typo right? @T.Z. — codeSeven, Oct 11 '16 at 05:54
i really appreciate ur answer but there's an error "Trying to get property of non-object ". though my fieldname is correct. :( — codeSeven, Oct 11 '16 at 06:12

PHP/MYSQL - Update subquery select with union

1 Answers1