• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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 )
0
afifosh
Asked:
afifosh
  • 2
1 Solution
 
Habib PourfardCommented:
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

0
 
sognoctCommented:
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

0
 
Anthony PerkinsCommented:
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

0
 
Anthony PerkinsCommented:
If you also need to join to CURRDATE do it this way:
UPDATE  s
SET     TOTALS = v.TOTALS
FROM    [STATISTICS] s
        INNER JOIN MYVIEW v ON s.REQPORT = v.REQPORT AND s.CURRDATE = v.CURRDATE
WHERE   s.CURRDATE = XX 

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now