help with updating a column to a dynamic variable

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

General SQL Server Forums

New to SQL Server Programming

help with updating a column to a dynamic variable

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-10-14 : 13:28:13

I am working on some code that creates a variable by counting offline databases. I want to record in a column in a table to the value of the variable. I do this in a loop and then increase the variable by one. There are 304 databases in my dataset (will change dynamically in the future of course). I am hitting HUGE problems though. Here is my code:


declare @cnt as int
declare @number as int
set @cnt = (select count(*) from usslcsqlt11.fakeadmin.dbo.databases where offlinedate is not null and inactiveflag=1 and serverid <> 9999)
set @number=1
	update usslcsqlt11.fakeadmin.dbo.databases
	set databaseid = null where offlinedate is not null and inactiveflag=1
	update usslcsqlt11.fakeadmin.dbo.databases
	set serverid = 9999 where databaseid=null
while @number < @cnt
begin
	update usslcsqlt11.fakeadmin.dbo.databases
	set databaseid = @number
	where ?????????????
	set @number=@number+1
end

The question marks are my problem. I have tried using subqueries that say something like "select top 1 databaseid where databaseid is null", I've tried joins, I've tried exists statements. All either fail, don't change anything, or change everything.

What is the best way to get this done?

Craig Greenwood

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-14 : 14:39:08

ummmmmm

first are we talking about sql server?

If so., what version

And if That is all true...

What the heck are you doing?

What is this then

usslcsqlt11.fakeadmin.dbo.databases

Obviously it's a linked server.db.owner.table????

But who created it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-14 : 14:46:18

Can you explain in words what you are trying to do?

I see that you are setting the DatabaseID to NULL and ServerID to 9999 and then in your while statement you are trying to set the DatabaseID to a number. Since you have already nullified the DatabaseID if you assign it a number that already exists will that cause issues?

craigwg
Posting Yak Master

154 Posts

Posted - 2009-10-14 : 17:29:51

Sorry folks! I think I over abbreviated. The code I posted above is a part of a larger stored procedure that works in tandum with three others. Here is the code with comments:

I have a database called admin, holds informatino about 80 or so servers. I have some cool stored procedures that dynamically pull information from servers and post it to admin. In this phase of the project I am accounting for offline databases. The problem is that ever online database has a databaseid. If a databasegoes offline, and then a new one is created its possible that the databaseid that is assigned to the new database will be the same as the databaseid as a previously deleted database.

To correct this problem we have a category called serverid that identifies each server. We created a dummy category (9999) that is a serverid simply to hold offline databases. Next problem is that when we try assign serverid 9999 to all offline databases it fails because there are 300 or so offline databases and invariably some of them have the same databaseid because they are coming from 80+ different servers.

Hope that all makes sense. Now to my code below, I am trying to capture all the offline databases, change their databaseid to null, then change the serverid of all databases that have null databaseids to 9999. Finally I want to loop through and add a databaseid back to the offline databases so that it isn't null, which will violate otherkeys and constraints. I don't much care what they are as long as they are unique. A consecutive series of numbers (1, 2, 3, 4, etc) will work great.

Whew!

Here is the code again. I hope that makes more sense!


declare @cnt as int
declare @number as int
set @cnt = (select count(*) from usslcsqlt11.fakeadmin.dbo.databases where offlinedate is not null and inactiveflag=1 and serverid <> 9999)
set @number=1
	update usslcsqlt11.fakeadmin.dbo.databases
	set databaseid = null where offlinedate is not null and inactiveflag=1
	update usslcsqlt11.fakeadmin.dbo.databases
	set serverid = 9999 where databaseid=null
while @number < @cnt
begin
	update usslcsqlt11.fakeadmin.dbo.databases
	set databaseid = @number
	where ?????????????
	set @number=@number+1
end

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-14 : 18:08:32

Maybe this'll help you on your way:

-- Setup test data
DECLARE @Databases TABLE
(
	ServerID INT,
	DatabaseID INT,
	InactiveFlag BIT,
	OfflineDate DATETIME
)

INSERT @Databases
SELECT 1, 1, 1, CURRENT_TIMESTAMP
UNION ALL SELECT 1, 2, 0, NULL
UNION ALL SELECT 1, 3, 1, CURRENT_TIMESTAMP
UNION ALL SELECT 2, 2, 1, NULL 

-- Get the MAX ID so there is no duplication:
DECLARE @Max INT
SET @Max = (SELECT MAX(DatabaseID) FROM @Databases)

-- Run Update:
UPDATE 
	T
SET 
	ServerID = 9999,
	DataBaseID = @Max + RowNum
FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (ORDER BY ServerID) AS RowNum
	FROM
		@Databases
	WHERE 
		OfflineDate IS NOT NULL 
		AND InactiveFlag = 1
) AS T


SELECT *
FROM @Databases

craigwg
Posting Yak Master

154 Posts

Posted - 2009-10-16 : 15:39:08

That did help, thanks. Not 100% clear on what is happening. Never used a subquery in a from statement! Didn't realize a rownumber function existed either. Cool trick! I altered your script a tiny bit and its working fantastic!

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources