Update Query for range of dates

[robojam]

Hi

 

I'm looking to update about 80 rows in a table where I need to sum some values from the source table.

 

Basically I want to run a query that does this:

 

UPDATE tblDaily

SET SALES = 'x', REDEMPTIONS = 'y', NET = (x + y)

WHERE FDATE > '13-Feb-2006'

AND FDATE < '14-Jun-2006'

 

The values of 'x' and 'y' are stored in another table, and I can get them using this query:

 

SELECT SUM(x), SUM(y), SUM(x + y)

FROM tblSTAGING

WHERE FDATE > '13-Feb-2006'

AND FDATE < '14-Jun-2006'

 

What I'm looking to do is to combine these queries so that I can run the UPDATE query with the SELECT query as a sub-query.

 

Does anyone know how to do this?

 

Thanks in advance!

[Puiu]

The solution that comes to me would be this:

 

create a stored proc where you have something like this:

 

declare @x as int, @y as int, @Sum as int

 

select @x= SUM(x)

FROM tblSTAGING

WHERE FDATE > '13-Feb-2006'

AND FDATE < '14-Jun-2006'

 

select @y = SUM(y)

FROM tblSTAGING

WHERE FDATE > '13-Feb-2006'

AND FDATE < '14-Jun-2006'

 

select @Sum = @x + @y

 

UPDATE tblDaily

SET SALES = @x, REDEMPTIONS = @y, NET = @Sum

 

 

this also might work:

 

update d set d.Sales = sum(t.x),

d.Redemptions = sum(t.y)

d.Net = ( sum(t.x) + sum(t.y) )

from tblStaging t, tblDaily d

where t.idSomething = d.idSomething

and t.FDATE > '13-Feb-2006'

AND t.FDATE < '14-Jun-2006'

 

however i don't think i tried this before...i'm not sure it's gonna work

try it and see if you get what you wanted

Edited June 23, 2006 by Puiu