July 8, 2009 at 4:11 pm
Still self-teaching....
I have two tables, one called invoices and one called trans
The invoices table among others has the fields 'invno' and 'net'
The trans table has 'ipay' and 'invno'
What I want to do is make the 'net' value of each line in the invoices table equal the sum of the ipay fields in the trans table for each relevant invno
What I have so far -
update invoices
set invoices.net = (select sum(trans.ipay)
from trans join invoices on trans.invno=invoices.invno
where invoices.invno = trans.invno)
The problem is that every invoice ends up with the total of the ipay from the entire trans table.
Hope this makes sense, any help greatly appreciated.
Matt
July 8, 2009 at 4:15 pm
Your code:
update invoices
set invoices.net = (select sum(trans.ipay)
from trans join invoices on trans.invno=invoices.invno
where invoices.invno = trans.invno)
What you want:
update invoices set
net = sum(t.ipay)
from
invoices i
inner join trans t
on (i.invno = t.invno)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply