Help with Update Query - 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

Help with Update Query

Author

Topic

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-03-04 : 12:41:47

Hi, I need to change 'name' in a free form field for multple records. I am trying to use this curson query. Please let me know if I can do it in a better manner. if not, then help me with Replace statement that is creating error

DECLARE merge_cursor CURSOR
FOR SELECT
[SNo] ,[COMMENT]
FROM dbo.table where SNo='99999'
--declare varibales
DECLARE @StuId CHAR(10)
DECLARE @Comm Varchar(8000)
--open cursor
OPEN merge_cursor
--read next
FETCH NEXT FROM merge_cursor INTO @StuId,@Comm
if @@FETCH_STATUS <> 0 Print 'No Records found'
WHILE @@FETCH_STATUS = 0 -- record found
BEGIN
Print @Comm
CASE when charindex('Shawnee',@Comm) <> 0 then REPLACE(@Comm,'Shawnee','Shawnee-Brienna') END
UPDATE dbo.table
SET [COMMENT]= @Comm
WHERE [SNo] = @StuId;
FETCH NEXT FROM merge_cursor INTO @StuId,@Comm
END;
CLOSE merge_cursor ;
DEALLOCATE merge_cursor ;

Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 45
Incorrect syntax near ';'.

Niki

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 12:51:39

1) I belive you should use PatIndex function instead of CharIndex.
2) Why you're updating using the cursor when you could simply do with with an update statement? I think you don't like your SQL server to be fast :)

Cheers
MIK

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-03-04 : 13:03:59

Thanks for tip on PATINDEX (I am new to SQL server)
I need to update multiple records where 'the name' may or may not exist in the comment, how would one update statement will work for all?

Niki

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-04 : 13:35:27

Best way is to post sample table structure (including data types of columns), sample data and wanted result in relation to the sample data.

Your cursor is doing (would do if it would work) the update only for rows where SNo='99999' - is that what you reallly want?

Some sample data would make it more clear for us...

Too old to Rock'n'Roll too young to die.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 13:36:28

I think all you need is a single update statement like shown below; try it in a test environment and see if it gives you the results you need.

UPDATE dbo.tbl SET
	Comment = REPLACE(Comment,'Shawnee','Shawnee-Brienna');

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-03-04 : 13:49:33

I made it so complicated because I got following error on simple update suggested by James K

Argument data type text is invalid for argument 1 of replace function.

Niki

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 13:57:15

paste the query here you are trying to execute along with error message

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 14:05:18

You might try this

UPDATE dbo.tbl SET
	Comment = REPLACE(CAST(Comment AS VARCHAR(MAX)),'Shawnee','Shawnee-Brienna');

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-03-04 : 14:27:14

Perfect! That worked. Thanks James K!

Niki

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-04 : 14:35:24

You are very welcome - glad to be of help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources