Modify a Table field by adding to it
I have Table1, where I want to modify previous_sum where previous_sum is the sum of the numbers field in Table 2 up to that specific date. Example:
Table1
Date___|___previous_sum
01/01__|___20
01/02__|___50
01/03__|___100
Table2
Date___|___numbers
01/01__|___20
01/02__|___30
01/03__|___50
So, previous_sum is 0 in the beginning but 开发者_如何学Pythondepending on what is in the numbers field up to that date, I want it to add correspondingly to previous_sum.
UPDATE table1
SET previous_sum =
COALESCE(
(
SELECT SUM(numbers)
FROM table2
WHERE table2.date <= table1.date
), 0)
I'm not quite sure what you're asking, but I think you mean something like this?
UPDATE Table1 SET Previous_Sum = Table2.numbers
FROM Table1
INNER JOIN Table2 ON Table1.Date = Table2.Date
P.S. if you need a SUM just add a group by and SUM the numbers
UPDATE Table1
SET [previous_sum] = (SELECT SUM(numbers) WHERE Table2.Date <= Table1.Date)
精彩评论