Solved

tsql update table on select

Posted on 2012-12-29
4
489 Views
Last Modified: 2012-12-30
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
Comment
Question by:afifosh
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38728956
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
 
LVL 9

Expert Comment

by:sognoct
ID: 38729053
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38729275
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38729278
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question