Using Declare variable within update statement

I need to change the text within a couple of records where the data contains the word "Annual" and change it to "Open"

declare @var1
@var1 = 'Open'

SELECT [ResourceSet]
      ,[ResourceType]
      ,[CultureCode]
      ,[ResourceKey]
      ,[ResourceValue]
      ,[Category]
      ,[Translated]
  FROM [bcdevtraining].[dbo].[cm_resource_core]
--UPDATE [bcdevtraining].[dbo].[cm_resource_core]
--   SET [ResourceValue] = 'Open Enrollment'
where [ResourceValue] like '%Annual Enrollment%'
GO
First record  = It's {0} &  @var1 &  'Enrollment through {1}. This is your opportunity to change your benefit elections, add or modify dependents, and change beneficiaries for {0}.
Second record = Begin Your Annual Enrollment Now!

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
Sure and sorry my bad for the first posting - this must do it - use the REPLACE sql function

UPDATE [bcdevtraining].[dbo].[cm_resource_core]
  SET [ResourceValue] = replace([ResourceValue],'Annual Enrollment','Open Enrollment')
WHERE [ResourceValue]='%Annual Enrollment%'

OR you can reduce it to just the two words you mentioned:


UPDATE [bcdevtraining].[dbo].[cm_resource_core]
  SET [ResourceValue] = replace([ResourceValue],'Annual ','Open ')
WHERE [ResourceValue]='%Annual%'
0
 
lcohanDatabase AnalystCommented:
Why do you need to use variable if that's all you need to do and an update like you described (and enclosed below) will do what you need to:

UPDATE [bcdevtraining].[dbo].[cm_resource_core]
  SET [ResourceValue] = 'Open Enrollment'
where [ResourceValue] like '%Annual Enrollment%'
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Is there a Way to do a Mass update searching for all instances of "Annual Enrollment" and update the just that portion of the text with "Open Enrollment"?

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the refresher - just a  little rusty.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.