| Author |
 Topic  |
|
|
Niki
Starting Member
45 Posts |
 Posted - 03/04/2013 : 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
Aged Yak Warrior
Pakistan
822 Posts |
Posted - 03/04/2013 : 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
Starting Member
45 Posts |
Posted - 03/04/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/04/2013 : 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
Flowing Fount of Yak Knowledge
1486 Posts |
Posted - 03/04/2013 : 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
Starting Member
45 Posts |
Posted - 03/04/2013 : 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
Aged Yak Warrior
Pakistan
822 Posts |
Posted - 03/04/2013 : 13:57:15
|
paste the query here you are trying to execute along with error message
Cheers
MIK |
|
|
|
James K
Flowing Fount of Yak Knowledge
1486 Posts |
Posted - 03/04/2013 : 14:05:18
|
You might try thisUPDATE dbo.tbl SET
Comment = REPLACE(CAST(Comment AS VARCHAR(MAX)),'Shawnee','Shawnee-Brienna'); |
|
|
|
Niki
Starting Member
45 Posts |
Posted - 03/04/2013 : 14:27:14
|
Perfect! That worked. Thanks James K!
Niki |
|
|
|
James K
Flowing Fount of Yak Knowledge
1486 Posts |
Posted - 03/04/2013 : 14:35:24
|
| You are very welcome - glad to be of help. |
|
|
| |
Topic |
|
Help with Update Query
