How to update from 1 tbl to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to update from 1 tbl to another

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-10-08 : 14:59:50

I know this is a simple question, but I guess I have forgotten how to do this.

I have a table with data in it, and I need to construct an UPDATE query to update certain rows.

The denormalized table is Tbl1(Key1, Key2, Key3, ValueA, ValueB)

The query I have to update this with is Qry1(Key1, Key2, ValueB)

I am looking for something with the following logic.
Consider this Pseudocode:

UPDATE Tbl1 SET ValueB = (
 SELECT Sum(X) FROM Qry1 WHERE Key1=Key1 and Key2=Key2 
 GROUP BY Key1, Key2
)

I expect all rows where the keys match to have the denormalized ValueB populated.
But I am getting an error stating that I can't add NULL values to Tbl1.ValueB.

Please let me know if you have any suggestions.

~ Shaun Merrill
Seattle, WA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-08 : 16:11:36

Does Tbl1.ValueB allow NULLs?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-08 : 16:14:00

Instead of your code, do something like this instead:

UPDATE Tbl1
SET ValueB = not sure what you want here cuz I don't know what X is in your pseudocode
FROM Tbl1 t
INNER JOIN Qry1 q ON t.Key1 = q.Key1 AND t.Key2 = q.Key2

Tara

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-10-08 : 17:20:27

Thanks. That was it.
FYI, Here is my code ... aren't you glad I didn't bother you with all this flack?


UPDATE Tbl1
  SET Tbl1.IncomingCalls = Q.IncomingCalls
FROM    (  SELECT 
              [DNIS], 
              [WeekOfYear], 
              [Year], 
              SUM(H.Calls) as IncomingCalls 
           FROM   
              (SELECT CommandID, DNIS FROM yadda-yadda ) Cmds 
           INNER JOIN
              dbo.BlockList BL  ON Cmds.CommandID = BL.CommandID 
           INNER JOIN
                dbo.History  H  ON BL.PatternID   = H.PatternID 
           INNER JOIN
                dbo.Date_Dim Dt ON H.DateID      = dt.DateID
           GROUP BY Cmds.[DNIS], dt.[Year], dt.[WeekOfYear]
	) Q
        INNER JOIN Tbl1 T 
            ON  Q.[DNIS]       = T.[DNIS] 
            AND Q.[Year]       = T.[Year] 
            AND Q.[WeekOfYear] = T.[WeekOfYear]

~ Shaun Merrill
Seattle, WA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources