0

I am a novice when it comes to SQL and require some advise.

I have a table xzy. Within this table I have multiple columns - wanting to update a section of one of the columns - defdetails.

Example of the DefDetails column:

<SYSTEM>
    <ScheduledItemDef ID="943bc40afb6beda35875f142469a526c35e264c6c6" Name="**3G Capped Users Check - Daily Task**" Version="1,0" SubType="" DoNotLocalize="FALSE" Scope="Core" Culture="Invariant" View="(None)" Disabled="FALSE" Units="Months">
        <LastModBy>USER</LastModBy>
        <LastModDateTime>2018-06-28T12:41:08</LastModDateTime>
        <Translations>
            <Translation Key="alias#en-US">Monthly - Reports - PRTG &amp; Supp Calls - CUSTOMER - TECHNICIAN</Translation><Translation Key="description#en-US">Monthly -Reports - PRTG &amp; Supp Calls - CUSTOMER - TECHNICIAN - New Business Development</Translation>
        </Translations>
        <ScheduleGroup ID="" />
        <ActionType>OneStep</ActionType>
        <ExceptionType>None</ExceptionType>
        <ExecutionType>Recurring</ExecutionType>
        <FirstRun DateTime="2018-08-03T08:00:00" UseServerTime="FALSE" TimeZoneId="South Africa Standard Time" TimeZoneName="(UTC+02:00) Harare, Pretoria" BaseUtcOffset="PT2H" />
        <InstanceNum>1</InstanceNum>
        <ContinueOnError Value="TRUE" />
        <ActionProperties>
            <Property Name="DefType">OneStepDef</Property>
            <Property Name="Folder">943bb517dde2d2838d511e4991ad53667caa4b06d0</Property>
            <Property Name="Owner">6dd53665c0c24cab86870a21cf6434ae</Property>
            <Property Name="Scope">Global</Property>
            <Property Name="DefId">943bc40c6a82e8f9c877514061bd3f031b5cd0279e</Property>
            <Property Name="ScopeOwner">(None)</Property>
        </ActionProperties>
        <Months Number="1" DayNumber="2" DayOfWeek="Monday" WeekOfMonth="NotSet" />
        <Recurrence StartDate="2018-08-03T14:41:00" MaxNumber="-1" />
        <TestOnly Value="FALSE" />
    </ScheduledItemDef>
</SYSTEM>

Example of the DefName column:

Monthly -Reports - PRTG & Supp Calls - Customer Name

The section I need to update is the Name="*" on line 2. I need to update only that section with Name="{the DefName Column}".

The DefDetails Name section always differs so I cannot hard code this into a replace and I need to update multiple records in the table this way.

Any help would be appreciated.

I have tried doing this manually, but the amount of work is tedious. I have tried using a replace but that requires me to hardcode my "Find String"and "Replace String" which is also not great as I need to update all records in the table and will need to do this multiple times through out the Software lifecycle. 

DECLARE @Text VARCHAR(MAX);

SET @Text = (SELECT TOP 150 DefDetails FROM SYSTEMScheduler)

SELECT 
    SUBSTRING(@Text, CHARINDEX('Name="', @Text),
    CHARINDEX('" Version', @text) - CHARINDEX('Name="', @Text) + LEN('"')) AS DefDetailsName
FROM
    [dbo].[SYSTEMScheduler] [DefDetails]

The DefDetails column should be updated below:

<SYSTEM>
    <ScheduledItemDef ID="943bc40afb6beda35875f142469a526c35e264c6c6"  Name="**Monthly -Reports - PRTG & Supp Calls - Customer Name**" Version="1,0" SubType="" DoNotLocalize="FALSE" Scope="Core" Culture="Invariant" View="(None)" Disabled="FALSE" Units="Months">
        <LastModBy>USER</LastModBy>
        <LastModDateTime>2018-06-28T12:41:08</LastModDateTime>
        <Translations>
            <Translation Key="alias#en-US">Monthly - Reports - PRTG &amp; Supp Calls - CUSTOMER - TECHNICIAN</Translation>
            <Translation Key="description#en-US">Monthly -Reports - PRTG &amp; Supp Calls - CUSTOMER - TECHNICIAN - New Business Development</Translation>
        </Translations>
        <ScheduleGroup ID="" />
        <ActionType>OneStep</ActionType>
        <ExceptionType>None</ExceptionType>
        <ExecutionType>Recurring</ExecutionType>
        <FirstRun DateTime="2018-08-03T08:00:00" UseServerTime="FALSE" TimeZoneId="South Africa Standard Time" TimeZoneName="(UTC+02:00) Harare, Pretoria" BaseUtcOffset="PT2H" />
        <InstanceNum>1</InstanceNum>
        <ContinueOnError Value="TRUE" />
        <ActionProperties>
            <Property Name="DefType">OneStepDef</Property>
            <Property Name="Folder">943bb517dde2d2838d511e4991ad53667caa4b06d0</Property>
            <Property Name="Owner">6dd53665c0c24cab86870a21cf6434ae</Property>
            <Property Name="Scope">Global</Property>
            <Property Name="DefId">943bc40c6a82e8f9c877514061bd3f031b5cd0279e</Property>
            <Property Name="ScopeOwner">(None)</Property>
        </ActionProperties>
        <Months Number="1" DayNumber="2" DayOfWeek="Monday" WeekOfMonth="NotSet" />
        <Recurrence StartDate="2018-08-03T14:41:00" MaxNumber="-1" />
        <TestOnly Value="FALSE" />
    </ScheduledItemDef>
</SYSTEM>

Essentially I would like to do something as follows:

Select substring between Name=" and "Version and replace with Column DefName

I know that is oversimplifying but that is in short what I would like to do.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sharon Perkins
  • 1
  • 1
  • Could you check if this link works? Typically you are asking to update the row using the column from same table? https://stackoverflow.com/questions/16180589/sql-server-update-values-from-one-column-in-same-table-to-another – User_CO Dec 28 '18 at 06:35
  • For me it is unclear what are you trying to achieve, even items that you show as part of the solution "SET @Text = (SELECT TOP 150 DefDetails FROM SYSTEMScheduler)" fail as cannot assign more than 1 row to a variable. Could you please provide a simplified example for your set of data with an explanation of the expected results? – Angel M. Dec 28 '18 at 07:09
  • @AngelM. I would essentially like to update only the Name=" to "Version section of the string in the defdetails to the defname column. Only the text within the " " needs to be udpdated. This needs to be done for all the rows in the table. The substring I need to update differs each time (meaning the text I need to replace) so I cannot hardcode the replacement string. I would like to know how to replace a section (substring) of the string with another column value, this needs to happen for all the records in the table. – Sharon Perkins Dec 28 '18 at 07:46

0 Answers0