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

[SQL_n00b]

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!

[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...

[kplcjl]

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:

--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?

[cheapterp]

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

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!

[kplcjl]

...
--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.

[kplcjl]

Back to my code example:

...
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.