Link to home
Start Free TrialLog in
Avatar of Member_2_6478753
Member_2_6478753Flag for United States of America

asked on

tsql update table on select

i have a table statistics :

with fiELD day, CURRDATE REQPORT  TOTALS

I WANT TO UPDATE TOTALS EVERY ON HOURRS USING SQL AGENT

I HAVE A VIEW SELECT

I WANT TO UPDATE THE VALUE OF TOTALS FROM THIS VIEWS

UPDATE STATISTICS SET TOTALS = ( )
WHERE CURRDATE = XX AND REQPORT =

FROM ( SELECT CURRATE,REQPORT,TOTALS FROM MYVIEW )
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

UPDATE  [STATISTICS]
SET     TOTALS = T.Totals
FROM    (SELECT CURRATE, REQPORT, TOTALS FROM MYVIEW) T
WHERE   CURRDATE = XX
        AND REQPORT = T.REQPORT

Open in new window

update t1 
set totals =t2.totals
from STATISTICS t1
INNER JOIN (SELECT CURRATE, REQPORT, TOTALS FROM MYVIEW)  t2 ON t1.CURRDATE = t2.currate AND t1.report = t2.reqport

Open in new window

Or simply:
UPDATE  s
SET     TOTALS = v.TOTALS
FROM    [STATISTICS] s
        INNER JOIN MYVIEW v ON s.REQPORT = v.REQPORT
WHERE   s.CURRDATE = XX 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial