Problem with update statement - 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

General SQL Server Forums

New to SQL Server Programming

Problem with update statement

Author

Topic

mmckimson
Starting Member

6 Posts

Posted - 2011-07-06 : 02:35:42

While I have developed a script that works perfectly using the MERGE statement, my client is using SQL Server 2005 and for some reason the code below which I thought should only update each record one record at a time seems to be updating all of the records with the same value. Here's the code I've started with:

use MH_DataMart

DECLARE @PropertyID nvarchar(255)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT Property_ID from temp_MHPricingDataLoad2, dim_MH_Properties WHERE HotelCode = MH_ID

OPEN c1

FETCH NEXT FROM c1
INTO @PropertyID

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE temp_MHPricingDataLoad2 SET PropertyID = @PropertyID

FETCH NEXT FROM c1
INTO @PropertyID

END

CLOSE c1
DEALLOCATE c1

What of course I'm trying to do is read each record and see if there is a matching PropertyID for that record, and if so, update the record. Instead of seeing multiple PropertyID's, this query is updating the ID for all records, and then writing over it again for the next ID it finds.

Any help people can provide would be appreciated!

Mike

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 02:41:28

With the given information we have to guess!
Please show us the table structure of temp_MHPricingDataLoad2 and dim_MH_Properties,
some sample data,
the condition when to update the Property_ID,
the wanted result.

I am absolut sure there is no need for a cursor.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 02:43:55

quote:

UPDATE temp_MHPricingDataLoad2 SET PropertyID = @PropertyID

where is the WHERE clause ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-06 : 02:45:29

Why are you using cursor ?

I don't see anything that can't be easily done with SET BASE solution rather than using cursor

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 02:49:41

It would really help if you could show us the MERGE-statement you have created. The following is more or less a wild guess but this is to illustrate an update completely without the cursor:

UPDATE a
	SET a.PropertyID = b.PropertyID
FROM temp_MHPricingDataLoad2 a
	INNER JOIN dim_MH_Properties b
		ON a.HotelCode = b.MH_ID

And btw: the reason your cursor doesn't work is that the update statement doesn't have a where-clause. You are updating every single row in the table for each iteration of the cursor!

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

mmckimson
Starting Member

6 Posts

Posted - 2011-07-06 : 03:04:10

Lumbago,

Your wild guess was right on actually, other than not updating the table with the correct data (but of course you were guessing!!). Thanks for your help.

Mike

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 03:27:42

Excellent

Now the point to be noted here for your future reference: cursors are the devil!

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-06 : 03:38:47

quote:
Originally posted by Lumbago

Excellent Now the point to be noted here for your future reference: cursors are the devil!

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Well that's true!
Another future point is: post more information so we do not have to guess.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources