Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1071

Nov 5th, 2009

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

Expand Post »

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!

Similar Threads

MYSQL: update all fields in a table (increase value by one) in PHP
update mysql table using javascript in PHP
How to Update a Data Table? in ASP.NET
server notify client to update MySQL table in C
How can i deledet and update from table ??????? in PHP

SQL_n00b

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

23 posts
since Apr 2009

Permalink

Nov 5th, 2009

-1

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

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

peter_budo

Moderator

Featured Poster

Reputation Points: 2786

Solved Threads: 874

Code tags enforcer

Offline

6,659 posts
since Dec 2004

Permalink

Nov 5th, 2009

-1

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

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:

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; Nov 5th, 2009 at 7:59 pm. Reason: Remove pasted data

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

Permalink

Nov 6th, 2009

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

Click to Expand / Collapse Quote 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

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!

cheapterp

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

33 posts
since Jun 2008

Permalink

Nov 6th, 2009

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

Click to Expand / Collapse Quote originally posted by cheapterp ...

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.

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

Permalink

Nov 6th, 2009

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

Back to my code example:

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.

kplcjl

Reputation Points: 14

Solved Threads: 12

Junior Poster

Offline

146 posts
since Sep 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: LEFT JOIN problem

Next Thread in MS SQL Forum Timeline: Retrieve row based column having null value in sql2000

DaniWeb Message

Cancel Changes