none
add a data to table from another table with sum function

    Question

  • hello everyone.

    İ have 2 tables calisanlar and Siparis_onaylanan.In calisanlar table there are calisan_no,adet and etc. columns.In Siparis_onaylanan table there are Siparis_id,urun,adet,calisan_no and etc. columns.I want to add data adet in calisanlar from sum(adet) Siparis_onaylanan.

    For example

    Caner Ünver's calisan_no(worker_id) 147 and add a delivery 5 banana and 4 watermelon.I want to add this numbers of items to calisanlar's adet

    i wanna codes like that

    update calisanlar set adet=select sum(adet) from Siparis_onaylanan where Siparis_onaylanan.calisan_no=calisanlar.calisan_no

    but it doesn't work :)

    Sunday, July 28, 2013 8:20 PM

Answers

  • ;With cte As
    (Select calisan_no, Sum(adet) As Sum_adet
    From Siparis_onaylanan
    Group By calisan_no)
    Merge calisanlar c
    Using cte
    On c.calisan_no = cte.calisan_no
    When Matched Then Update Set adet = cte.Sum_adet;
    

    Tom
    Sunday, July 28, 2013 9:54 PM

All replies

  • ;With cte As
    (Select calisan_no, Sum(adet) As Sum_adet
    From Siparis_onaylanan
    Group By calisan_no)
    Merge calisanlar c
    Using cte
    On c.calisan_no = cte.calisan_no
    When Matched Then Update Set adet = cte.Sum_adet;
    

    Tom
    Sunday, July 28, 2013 9:54 PM
  • Öncelikle merhabalar.

    UPDATE calisanlar
      set adet += S.adet
      from calisanlar as C inner join
           (select X.calisan_no, sum(X.adet) as adet
              from Siparis_onaylanan as X
              group by X.calisan_no) as S on C.calisan_no=S.calisan_no;

    José Diz.


    Belo Horizonte, MG - Brasil
    Mark as answered if this post solved your problem and Vote as helpful if this post was useful.

    • Proposed as answer by Jose.Diz Sunday, July 28, 2013 11:44 PM
    • Edited by Jose.Diz Monday, July 29, 2013 12:50 AM turco
    Sunday, July 28, 2013 11:37 PM
  • Tom thank you very much you are best :)
    Monday, July 29, 2013 12:20 PM