MS SQL

MS SQL RSS

'UPDATE'ing table with same value over and over again!

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Apr 2009

Posts: 16

Reputation: SQL_n00b is an unknown quantity at this point

Solved Threads: 1

SQL_n00b

Offline

Newbie Poster

'UPDATE'ing table with same value over and over again!

21 Days Ago

One of the UPDATE queries I am trying to run is not quite working the way I want it to. I know this query is the culprit, but can't quite figure out how to fix it! Here's the story:
-------------------------------------------------------
I have 3 tables: let's call them tblUsers, tblDueVisits and tblWindows
tblUsers: UserID (PK, int), FirstName (char), LastName (char), BirthDate (smalldatetime), etc.
tblDueVisits: UserID (int), DueDate (smalldatetime), WindowName(char). UserID + DueDate together make up the key for this table.
tblWindows: WindowID (int), WindowName (varchar)
tblWindows stores a pre-defined maximum number of records (say 10). This table will, at least, theoretically never change.
Now this is what I have to do:
• Check how many records a particular User ID (say ID # 1) has in tblDueVisits. For the sake of this example assume there are 5 records.
• Next, SELECT the TOP 5 WindowName from tblWindows.
• UPDATE tblDueVisits to store the WindowName in tblDueVisits WHERE UserID = 1.
(The front-end user selects an ID# from a web page’s drop down box)
-----------------------------------------------------
I have written the T-SQL loop statements to do this, but somehow the UPDATE doesn’t work. Specifically, what happens is that the same WindowName gets written 5 times rather than writing the 5 different WindowNames once each!!

Let me know if I wasn't totally clear about something, or if you need me to post the code for this.

Thanks!

•

Join Date: Dec 2004

Posts: 4,188

Reputation: peter_budo has much to be proud of

Solved Threads: 482

peter_budo peter_budo is offline

Offline

Code tags enforcer

21 Days Ago

So if you have written that code why didn't you posted so somebody can have look at it and tell you what can be wrong?
So far it does look like you posted your assignment and expect somebody else do it for you...

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Sep 2009

Posts: 42

Reputation: kplcjl is an unknown quantity at this point

Solved Threads: 5

kplcjl

Offline

Light Poster

-1

21 Days Ago

1. It is poor design to have a character field value added to a table that is contained in another table instead of referencing the table (IE WindowID)
2. It is poor design to use (char) for fields that don't have a fixed number of characters
3. It is poor design to not use an order by statement when you aren't using selection criteria and only taking a piece of the information.
That said:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
--initial test tables
SET NOCOUNT ON
DECLARE @tblUsers TABLE (UserID int PRIMARY KEY, FirstName varchar(20), LastName varchar(20), BirthDate smalldatetime)
DECLARE @tblDueVisits TABLE (UserID int, DueDate smalldatetime, WindowName varchar(20) NULL)
DECLARE @tblWindows TABLE (WindowID int identity, WindowName varchar(20) NULL)
--initialize tables
declare @dtnow smalldatetime, @id int,@txt varchar(3),@rw int 
SELECT @dtnow='20091105',@id=1
WHILE @id < 100
BEGIN
	SET @txt=@id
	INSERT INTO @tblUsers VALUES (@id, 'First' + @txt, 'LastName' + @txt, dateadd(year,-@id,@dtnow))
	INSERT INTO @tblWindows VALUES ('WindowName'+@txt)
	SELECT @id=@id*2,@rw=@@identity
	WHILE @rw > 0
	BEGIN
		INSERT INTO @tblDueVisits VALUES (@id, dateadd(day,@id*@rw,@dtnow),NULL)
		SET @rw=@rw-1
	END
END
SET nocount off
--SELECT * FROM @tblUsers
--SELECT * FROM @tblWindows
--SELECT * FROM @tblDueVisits
SET NOCOUNT ON
-- Process requirement
SET @id=16
DECLARE @tbl1 TABLE (ID1 int IDENTITY, DueDate smalldatetime)
DECLARE @tbl2 TABLE (ID2 int IDENTITY, WindowName varchar(20) NULL)
INSERT INTO @tbl1 SELECT DueDate FROM @tblDueVisits WHERE UserID=@id
SELECT @rw=@@ROWCOUNT
INSERT INTO @tbl2 SELECT TOP (@rw) WindowName FROM @tblWindows
UPDATE @tblDueVisits SET WindowName=c.WindowName
FROM @tblDueVisits a
JOIN @tbl1 b ON a.DueDate=b.DueDate AND UserID=@id
JOIN @tbl2 c ON ID1=ID2
SELECT * FROM @tblDueVisits WHERE UserID=@id
--initial test tables
SET NOCOUNT ON
DECLARE @tblUsers table (UserID int primary key, FirstName varchar(20), LastName varchar(20), BirthDate smalldatetime)
DECLARE @tblDueVisits table (UserID int, DueDate smalldatetime, WindowName varchar(20) null)
DECLARE @tblWindows table (WindowID int identity, WindowName varchar(20) null)
--initialize tables
declare @dtnow smalldatetime, @id int,@txt varchar(3),@rw int 
select @dtnow='20091105',@id=1
WHILE @id < 100
BEGIN
	set @txt=@id
	insert into @tblUsers values (@id, 'First' + @txt, 'LastName' + @txt, dateadd(year,-@id,@dtnow))
	insert into @tblWindows values ('WindowName'+@txt)
	select @id=@id*2,@rw=@@identity
	WHILE @rw > 0
	BEGIN
		insert into @tblDueVisits values (@id, dateadd(day,@id*@rw,@dtnow),null)
		set @rw=@rw-1
	END
END
set nocount off
--SELECT * FROM @tblUsers
--SELECT * FROM @tblWindows
--SELECT * FROM @tblDueVisits
SET NOCOUNT ON
-- Process requirement
SET @id=16
DECLARE @tbl1 table (ID1 int IDENTITY, DueDate smalldatetime)
DECLARE @tbl2 table (ID2 int IDENTITY, WindowName varchar(20) null)
INSERT INTO @tbl1 SELECT DueDate FROM @tblDueVisits WHERE UserID=@id
SELECT @rw=@@ROWCOUNT
INSERT INTO @tbl2 SELECT TOP (@rw) WindowName from @tblWindows
Update @tblDueVisits set WindowName=c.WindowName
from @tblDueVisits a
join @tbl1 b on a.DueDate=b.DueDate and UserID=@id
join @tbl2 c on ID1=ID2
select * from @tblDueVisits where UserID=@id

Is this what you are looking for?

Last edited by kplcjl; 21 Days Ago at 7:59 pm. Reason: Remove pasted data

•

Join Date: Jun 2008

Posts: 28

Reputation: cheapterp is an unknown quantity at this point

Solved Threads: 2

cheapterp

Offline

Light Poster

20 Days Ago

•

Originally Posted by peter_budo

So if you have written that code why didn't you posted so somebody can have look at it and tell you what can be wrong?
So far it does look like you posted your assignment and expect somebody else do it for you...

You're right, peter_budo. I should have posted the code first time around. Anyway, here it is now

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
DECLARE @VisitType varchar(7)	--The Visit Name with which tblWindows is to be updated
 
--DECLARE @counter int	--Counter to check if #Visits is reached 
--SET @counter = 1
 
DECLARE @NumberOfVisits int -- VAR to store the #Visits the ID has 
SET @NumberOfVisits = (SELECT COUNT(Window) FROM tblDueVisits WHERE UserID = 1) --typically the UserID will be picked up from the webpage form.
 
--Get the different Visit Types
DECLARE cursorVisitNames CURSOR FOR SELECT TOP (@NumberOfVisits) VisitName FROM tblDueVisits ORDER BY VisitName
 
OPEN cursorVisitNames
FETCH NEXT FROM cursorVisitNames INTO @VisitType
SET NOCOUNT ON
WHILE @@Fetch_Status = 0
	BEGIN
		PRINT @VisitType  --Verify correct number of visits is selected
 
		UPDATE tblWindows SET Visit = @VisitType WHERE UserID = 1
		--SET @counter = @counter+1
 
		FETCH NEXT FROM cursorVisitNames INTO @VisitType
	END
SET NOCOUNT OFF
 
CLOSE cursorVisitNames
DEALLOCATE cursorVisitNames
DECLARE @VisitType varchar(7)	--The Visit Name with which tblWindows is to be updated

--DECLARE @counter int	--Counter to check if #Visits is reached 
--SET @counter = 1

DECLARE @NumberOfVisits int -- VAR to store the #Visits the ID has 
SET @NumberOfVisits = (SELECT COUNT(Window) from tblDueVisits WHERE UserID = 1) --typically the UserID will be picked up from the webpage form.

--Get the different Visit Types
DECLARE cursorVisitNames CURSOR FOR SELECT TOP (@NumberOfVisits) VisitName FROM tblDueVisits ORDER BY VisitName

OPEN cursorVisitNames
FETCH NEXT FROM cursorVisitNames INTO @VisitType
SET NOCOUNT ON
WHILE @@Fetch_Status = 0
	BEGIN
		PRINT @VisitType  --Verify correct number of visits is selected
		
		UPDATE tblWindows SET Visit = @VisitType WHERE UserID = 1
		--SET @counter = @counter+1
		
		FETCH NEXT FROM cursorVisitNames INTO @VisitType
	END
SET NOCOUNT OFF

CLOSE cursorVisitNames
DEALLOCATE cursorVisitNames

Obviously, the UPDATE statement I have is a disaster. But anything else that I have tried won't work!

•

Join Date: Sep 2009

Posts: 42

Reputation: kplcjl is an unknown quantity at this point

Solved Threads: 5

kplcjl

Offline

Light Poster

20 Days Ago

•

Originally Posted by cheapterp

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)
...
--Get the different Visit Types
DECLARE cursorVisitNames CURSOR FOR SELECT TOP (@NumberOfVisits) VisitName FROM 
ORDER BY VisitName
 
...
FETCH NEXT FROM cursorVisitNames INTO @VisitType
...
		UPDATE tblWindows SET Visit = @VisitType WHERE UserID = 1
... --Get the different Visit Types DECLARE cursorVisitNames CURSOR FOR SELECT TOP (@NumberOfVisits) VisitName FROM ORDER BY VisitName ... FETCH NEXT FROM cursorVisitNames INTO @VisitType ... UPDATE tblWindows SET Visit = @VisitType WHERE UserID = 1
... tblWindows stores a pre-defined maximum number of records (say 10). This table will, at least, theoretically never change. ...

...

So, you have a table that will never change that you are updating... Huh. WHERE UserID = 1 when the table doesn't contain UserID and you are puzzled because it doesn't work? OK lets switch the tables in the cursor and the update so it matches your original text.

UPDATE tblDueVisits SET Visit = @VisitType WHERE UserID = 1

This table according to your statement is a two column key, so there are multiple records where UserID = 1, so of course every record gets set to the same visit type. You need to use BOTH key values in your where statement to identify 1 record you want to update.

You can set up a second cursor on tblDueVisits to retrieve the date where UserID = 1. Fetch next on it inside your existing loop (ONE time only --- DO NOT loop inside the loop.) and use both key fields to update only one record per loop.

I recommend you review the code I supplied. Toggle it to text, copy and paste into EM, fix my typo and run it. (Or don't fix it. You still get an example of multiple rows getting updated in one statement with different values for every row.) You can't hurt anything because it only touches temp tables.

It doesn't use CURSORS because everything you read about will tell you this is a poor choice. (CURSOR does have its place.) I don't use count because I still need to retrieve every key field to use them in the combination of key fields. So I put them in a temp table and use @@rowcount instead.

OK, the data is bogus, it still displays the process.

•

Join Date: Sep 2009

Posts: 42

Reputation: kplcjl is an unknown quantity at this point

Solved Threads: 5

kplcjl

Offline

Light Poster

20 Days Ago

Back to my code example:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
...
UPDATE @tblDueVisits SET WindowName=c.WindowName
FROM @tblDueVisits a
JOIN @tbl1 b ON a.DueDate=b.DueDate AND UserID=@id
JOIN @tbl2 c ON ID1=ID2
...
...
Update @tblDueVisits set WindowName=c.WindowName
from @tblDueVisits a
join @tbl1 b on a.DueDate=b.DueDate and UserID=@id
join @tbl2 c on ID1=ID2
...

OK, what is going on.
@tbl1 has an identity field and a list of every DueDate in @tblDueVisits where UserID=@id.
Since I didn't bother making a key for @tblDueVisits I instead made sure every DueDate was unique per UserID when I created the data so every date in both tables are an exact match.
@tbl2 has an identity field and a list of WindowNames that has the same number of rows in @tbl1 but from the static table data.
I am taking advantage of how an identity field works. Without modifiers, it will always start at 1 and always increase in value by 1. Therefore there will be a 1 to 1 match where ID1=ID2. Therefore there will also be a 1 to 1 where a.DueDate=b.DueDate and UserID=@id
So each WindowName is unique on each row after the update where UserID=@id.
I used 16 instead of 1 because it has multiple rows for the id in @tblDueVisits.