SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Update Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Niki
Yak Posting Veteran

51 Posts

Posted - 03/04/2013 :  12:41:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  12:51:39  Show Profile  Reply with Quote
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
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 03/04/2013 :  13:03:59  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 03/04/2013 :  13:35:27  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 03/04/2013 :  13:36:28  Show Profile  Reply with Quote
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');
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 03/04/2013 :  13:49:33  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  13:57:15  Show Profile  Reply with Quote
paste the query here you are trying to execute along with error message

Cheers
MIK
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 03/04/2013 :  14:05:18  Show Profile  Reply with Quote
You might try this
UPDATE dbo.tbl SET
	Comment = REPLACE(CAST(Comment AS VARCHAR(MAX)),'Shawnee','Shawnee-Brienna');
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 03/04/2013 :  14:27:14  Show Profile  Reply with Quote
Perfect! That worked. Thanks James K!

Niki
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 03/04/2013 :  14:35:24  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000