mercredi 2 mars 2016

Sqlite - update one table based on another table

I have 2 tables T1 (month+year is unique)

month year amount category

  • 10 2015 5000 A
  • 10 2015 5000 B
  • 11 2015 4000 A

T2 month year amount flag

  • 10 2015 100 Y
  • 10 2015 200 Y
  • 10 2015 500 N
  • 11 2015 200 Y

I want to update the amount field of T1 as follows for those rows which have category = 'Y' T1.amount = T1.amount - (sum of amount in T2 for matching month,year and having flag = Y)

So after update T1 shall look like this

month year amount category

  • 10 2015 4700 A (amount reduced by 300)
  • 10 2015 5000 B (no change)
  • 11 2015 3800 A (amount reduced by 200)

Kindly help

Aucun commentaire:

Enregistrer un commentaire