Loop through query to update another table 1

[ks1392]

Hi folks, I have a question re: query loops, variables and table updates. I'm currently performing a large table update (1.2M records) via a ColdFusion template. I would like to change this update so I can perform it using a stored procedure and/or DTS, leaving CF out of the picture. Here's how it works:

The purpose of the update is to update a table with expenses which has expense data by manager. This is tracked by manager by month, with the expense tracking being handled by a hierarchical nine character code called a responsibility code (RC). These RCs change frequently, so I have users update a mapping table that contains the manager ID numbers and any old RCs that belong to them.

The three tables involved:
Managers - contains the current manager records only
RCMap - contains the current manager IDs with the associated old RCs
Expenses - contains monthly expenses with current RCs only.

Here's what I'm doing in ColdFusion:

1. Query the Managers table Inner joined with the RCMap table, getting only managers who have mapped cost centers.

SELECT DISTINCT Managers.UID, Managers.RC
FROM Managers INNER JOIN RCMap
ON Managers.UID = RCMap.UID

2. Loop through the first query and obtain a list of all the old RCs I need to update in the Expenses table

SELECT OldRC FROM RCMap WHERE UID = var(Query1.UID)

3. Still inside the initial query loop, run an update query on the Expenses table to update all the old RCs with the current RC. I'm using a CF function, QuotedValueList, which turns the results of the second query to into a list WITHOUT having to loop through that second query.

UPDATE Expenses SET NewRC = var(Query1.RC)
WHERE RC IN var(QuotedValueList(Query2.OldRC))


This third step is the crux of the whole deal because I don't have to loop through Query 2 to do the update, thereby saving me quite a bit of processing time.

So, can this type of script be run inside a stored procedure? I don't even need to pass any variables, the variables are all query result set generated.

Thanks in advance!
Kerr

(For you CF'ers I know this is not syntactically correct in CF but I wanted to be clear on where I'm using query driven variables to see how this can be done using SQL Server only.)

 

[r937]

"So, can this type of script be run inside a stored procedure?"

yes, and it'll be a darned sight more efficient that repetitively calling the database from the scripting language inside a loop


rudy

 

[ks1392]

That's good to hear - now my next question. I'm rather unfamiliar with the syntax of stored procedures, etc. From what I've read so far I understand that I will have to set a cursor to loop through the query, but I'm unclear on how to perform an update with the result set from query 2 without having to actually loop through query 2. Any advice?

Thanks,
Kerr

 

[SQLSister]

I don't think you need to loop at all just join the tables correctly. Give us the structure of the tables and maybe we can help you out.

 

[ks1392]

I would be very curious to see how how what I describe can be implemented without looping.

Table structure:

CREATE TABLE [dbo].[Expenses] (
[ExpenseAmt] [decimal](17, 2) NULL ,
[RC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExpenseDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Managers] (
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UID] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RCMap] (
[MapID] [int] IDENTITY (1, 1) NOT NULL ,
[UID] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldRC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

 

[SQLSister]

Question? Does only one manager at a time have a particular Rc or could more than one have it? Also could a manager have more than one current RC? I'm trying to create some data to figure out the query from.

 

[SQLSister]

Not sure if this will work, but it seemed to do what you were asking with the sample data I made up. So try it, it may give you a place to start.

Update Expenses
Set NEWRc =
Managers.RC
FROM RCMap INNER JOIN
Managers ON RCMap.UID = Managers.UID INNER JOIN
Expenses ON RCMap.OldRC = Expenses.RC

 

[ks1392]

Sorry! I should've posted some test data for you!

Answer 1: The data involved will never have more than one manager per RC.

Answer 2: A manager can have only one RC at a given point in time.

Here is some real world data:
Expenses: (NewRC is populated already, my raw data blank but I wanted to show you the relationship)

145757.54|S0S307000|S0S60B000|2002-05-01
14371.64|S0S307100|S0S60B100|2002-02-01
63470.38|S0S307110|S0S60B110|2002-02-01
79610.40|S41107000|S0S60B000|2001-09-01
14847.30|S41107100|S0S60B100|2001-04-01
29487.24|S41107110|S0S60B110|2001-06-01

Managers:

WILLIAMS|TW6020|S0S60B000
MENOWN|JM4124|S0S60B100
BAKER|MB3535|S0S60B110

RCMap:

111|TW6020|S0S307000|2002-11-02
112|TW6020|S41107000|2002-11-02
2|JM4124|S0S307100|2002-10-31
56|JM4124|S41107100|2002-10-31
113|MB3535|S0S307110|2002-11-02
114|MB3535|S41107110|2002-11-02

 

[ks1392]

Hehe, you posted as I posted... I will give this a try and let you know how it works - thanks!

 

[ks1392]

SQLSister - did some verification against what I was doing and it worked great, saved lots of time too. Thanks for your help! I haven't seen a statement quite like that before; I didn't know you could update in that fashion without having do do some sort of SELECT subquery.

 

[SQLSister]

Beats the heck out of cursors and looping , huh?

 

[ks1392]

You're telling me, I can use this logic in many places in the app I'm working on - my update time dropped by 20% and takes up much less overhead as I'm only running one query.

Thanks again!