Stored Proc Update question - SQL Server Forums

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

SQL Server Development (2000)

Stored Proc Update question

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2004-12-06 : 14:45:58

I'm not so hot in SQL Stored Procedures yet so I need a hand with one.

I've got two tables. One table (LMS_Users) has a unique UserID field and stores the user's DeptID. The other table (LMS_TestSessions) also contains the UserID and the DeptID, however the DeptID is TODAY's DeptID and will not change when the user changes departemnts, so I can't relate the two columns.

How can I update the blank DeptID fields in LMS_TestSessions with the current DeptID values in LMS_Users?

Here's what I've got so far (doesn't work):

================================

CREATE PROCEDURE [dbo].[LMS_TestSessionDeptIDUpdate]
AS
UPDATE LMS_TestSessions
SET LMS_TestSessions.DeptID = LMS_Users.DeptID
FROM LMS_Users INNER JOIN LMS_TestSessions
ON (LMS_Users.UserID=LMS_TestSessions.UserID)
WHERE LMS_TestSessions.DeptID <> LMS_Users.DeptID
GO

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-06 : 16:02:19

If you post the DDL of the tables, some sample data in the form of DML and what you expect to happen as far as results go, I'm sure we can hook you up...

Brett

8-)

MrRay
Starting Member

28 Posts

Posted - 2004-12-06 : 17:43:20

quote:
Originally posted by X002548

If you post the DDL of the tables, some sample data in the form of DML and what you expect to happen as far as results go, I'm sure we can hook you up...

Thanks Brett,

The stuff is pretty basic.

From table LMS_Users:

USERID | DEPTID (plus other details)
User01 | 2
User02 | 2
User03 | 1
User04 | 3
User05 | 2
User06 | 3

From table LMS_Sessions:

USERID | DEPTID (plus other details)
User06 | ______
User02 | ______
User03 | ______
User02 | ______
User05 | ______
User06 | ______
User05 | ______
User01 | ______
etc.

I need to run a one-time stored procedure to put the current DEPTID values for each user in the LMS_Sessions table. Again, the data won't be connected to the original LMS_Users table, as we want to store the CURRENT UserID regardless of future department (DeptID) changes.

Can you hook me up with something? I thought I was on the right track with the code in my original posting but I got nothing... Not even an error message. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-06 : 17:47:22

If it's a one-time thing, then no reason to wrap in a stored procedure. Just run it from Query Analyzer. Try this:

UPDATE lts
SET DeptID = lu.DeptID
FROM LMS_TestSessions lts
INNER JOIN LMS_Users lu
ON lts.UserID = lu.UserID
WHERE lts.DeptID <> lu.DeptID

But do you even need the WHERE clause? Is there other data in there that you don't want to update?

Tara

MrRay
Starting Member

28 Posts

Posted - 2004-12-06 : 19:05:17

quote:
Originally posted by tduggan

If it's a one-time thing, then no reason to wrap in a stored procedure. Just run it from Query Analyzer. Try this:

UPDATE lts
SET DeptID = lu.DeptID
FROM LMS_TestSessions lts
INNER JOIN LMS_Users lu
ON lts.UserID = lu.UserID
WHERE lts.DeptID <> lu.DeptID

But do you even need the WHERE clause? Is there other data in there that you don't want to update?

Tara

Yeah, the where clause doesn't matter so much.

So basically I had the right code, but the tables were inverted? Here's what I got out of your sample:

CREATE PROCEDURE [dbo].[LMS_TestSessionDeptIDUpdate]
AS
UPDATE LMS_TestSessions
SET LMS_TestSessions.DeptID = LMS_Users.DeptID
FROM LMS_TestSessions
INNER JOIN LMS_Users
ON LMS_TestSessions.UserID = LMS_Users.UserID
WHERE LMS_TestSessions.DeptID <> LMS_Users.DeptID
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources