July 9, 2009 at 4:08 am
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 9, 2009 at 4:19 am
UPDATE invoices
SET net =
(
SELECT SUM(T1.ipay)
FROM trans T1
WHERE invoices.invno = T1.invno
)
-- you may want this to stop nulls when no trans for an invoice
-- WHERE EXISTS
-- (
-- SELECT *
-- FROM trans T2
-- WHERE invoices.invno = T2.invno
-- )
July 10, 2009 at 12:55 pm
Thanks Ken,
Looked like I was over complicating it !
Matt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply