CODEWITHSUNDEEP
sql-servert-sql
Blue high Mountain
Blue high Mountain

Reputation: 67

Select and update; SQL Server

I need to loop table tab_A entry by entry and update table tab_B according to content in each returned entry:

SELECT item, regBy, MAX(regTime) AS latestUpdateTime
FROM tab_A
GROUP BY item, regBy;

The result must be looped entry by entry and the following UPDATE must be performed for each entry:

UPDATE tab_B
SET lastUpdated = "data from latestUpdateTime in SQL above"
lastUpdBy = "data from regBy in SQL above"
WHERE item = "data from item in SQL above"

I'm not familiar with Transact SQL, so any help would be appreciated.

Upvotes: 2

Views: 780

Answers (2)

marc_s
marc_s

Reputation: 754338

If you're using SQL Server 2005 or newer, you can do something like this:

;WITH TableAData AS
(
   SELECT 
       item, regBy, regTime,
       RowNum = ROW_NUMBER() OVER(PARTITION BY item ORDER BY regTime DESC)
   FROM dbo.tab_A
)
UPDATE dbo.tab_B
SET 
    lastUpdated = a.regTime,
    lastUpdBy = a.regby
FROM TableAData a    
WHERE
    tab_B.item = a.item 
    AND a.RowNum = 1

Basically, this CTE (Common Table Expression) orders your data in such a way that for each item, a RowNum is calculated (with the most recent one getting RowNum = 1).

With this, you can easily update your tab_B in a single statement - no row-by-agonizing-row looping needed!

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can turn a regular select statement into an update statement quite easily by replacing the SELECT * with UPDATE table...

This allows you to first verify what will get updated before actually applying the update.

Select statement

SELECT *
FROM   tab_B b
       INNER JOIN (
          SELECT  item
                  , regBy
                  , MAX(regTime) AS latestUpdateTime
         FROM     tab_A
         GROUP BY 
                  item
                  , regBy
       ) a ON a.item = b.item

Update statement

UPDATE tab_B
SET    lastUpdated = a.latestUpdateTime
       , lastUpdBy = a.regBy
FROM   tab_B b
       INNER JOIN (
          SELECT  item
                  , regBy
                  , MAX(regTime) AS latestUpdateTime
         FROM     tab_A
         GROUP BY 
                  item
                  , regBy
       ) a ON a.item = b.item

Another way to verify the update and depending on your version would be by starting a transaction and using the OUTPUT clause.

BEGIN TRAN

UPDATE tab_B
SET    lastUpdated = a.latestUpdateTime
       , lastUpdBy = a.regBy
OUTPUT INSERTED.*
FROM   tab_B b
       INNER JOIN (
          SELECT  item
                  , regBy
                  , MAX(regTime) AS latestUpdateTime
         FROM     tab_A
         GROUP BY 
                  item
                  , regBy
       ) a ON a.item = b.item

ROLLBACK TRAN

Upvotes: 3

Related Questions