Replace specific part in my data

Question

I would like to know most efficient and secure way to replace some numbers. In my table i have two columns: Nummer and Vater. In Nummer column i store articles numbers. The one with .1 at the end is the 'main' article and rest are his combinations (sometimes main article doesn't contain combinations), all of specific makes it as concrete product with combinations. Numbers consist of 3-parts separated by 3 dots (always). Vater for all of them is always main article number as shown below:

Example 1:

Nummer        |   Vater
-------------------------------
003.10TT032.1   |  003.10TT032.1
003.10TT032.2L  |  003.10TT032.1
003.10TT032.UY  |  003.10TT032.1

Nummer column = varchar Vater column = varchar

I want to have possibility to change first 2 parts n.n

For example i want to say and send via sql query that i want to replace to: 9.4R53 Therefore based on our example the final results should be as follows:

Nummer    |   Vater
----------------------
9.4R53.1   |  9.4R53.1
9.4R53.2L  |  9.4R53.1
9.4R53.UY  |  9.4R53.1

Example 2:

Current:

Nummer        |   Vater
-------------------------------
12.90D.1    |  12.90D.1 
12.90D.089  |  12.90D.1 
12.90D.2    |  12.90D.1 

Replace to: 829.12

Result should be:

Nummer        |   Vater
-------------------------------
829.12.1    |  829.12.1 
829.12.089  |  829.12.1 
829.12.2    |  829.12.1 

I made queries as follows:

Example 1 query:

update temp SET Nummer = replace(Nummer, '003.10TT032.', '9.4R53.'),
                            Vater = replace(Vater, '003.10TT032.1', '9.4R53.1')
                            WHERE Vater = '003.10TT032.1'

Example 2 query:

update temp SET Nummer = replace(Nummer, '12.90D.', '829.12.'),
                            Vater = replace(Vater, '12.90D.1', '829.12.1')
                            WHERE Vater = '12.90D.1 '

In my database i have thousends of records therefore i want to be sure this query is fine and not having anything that could make potentially wrong results. Please of your advice whether can it be like this or not.

Therefore questions:

1. Is this query fine according to how my articles are stored? (want to avoid wrong replacments which could makes mess into production database data)

2. Is there better solution?

Answer 1

This is a separate answer to show how to split the product ID into separate sections - If you have to update sections of the product ID I think it is better t to store it in a separate columns:

DECLARE @ProductRef TABLE
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
SrcNummer VARCHAR(255), DisplayNummer VARCHAR(255), SrcVater VARCHAR(255), DisplayVater VARCHAR(255), 
NummerSectionA VARCHAR(255), NummerSectionB VARCHAR(255), NummerSectionC VARCHAR(255), 
VaterSectionA VARCHAR(255), VaterSectionB VARCHAR(255), VaterSectionC VARCHAR(255) )
INSERT INTO @ProductRef (SrcNummer, SrcVater ) VALUES ('003.10TT032.1','003.10TT032.1')
INSERT INTO @ProductRef (SrcNummer, SrcVater ) VALUES ('003.10TT032.2L','003.10TT032.1')
INSERT INTO @ProductRef (SrcNummer, SrcVater ) VALUES ('003.10TT032.UY','003.10TT032.1')
DECLARE @Separator CHAR(1) 
SET @Separator = '.'

;WITH SeparatorPosition (ID, SrcNummer, NumFirstSeparator, NumSecondSeparator, SrcVater, VatFirstSeparator, VatSecondSeparator) 
AS (    SELECT 
        ID,
        SrcNummer, 
        CHARINDEX(@Separator,SrcNummer,0) AS NumFirstSeparator,
        CHARINDEX(@Separator,SrcNummer,  (CHARINDEX(@Separator,SrcNummer,0))+1  ) AS NumSecondSeparator,
        SrcVater, 
        CHARINDEX(@Separator,SrcVater,0) AS VatFirstSeparator,
        CHARINDEX(@Separator,SrcVater,  (CHARINDEX(@Separator,SrcVater,0))+1  ) AS VatSecondSeparator
    FROM @ProductRef )
UPDATE @ProductRef
SET 
    NummerSectionA = SUB.NummerSectionA , NummerSectionB = SUB.NummerSectionB , NummerSectionC = SUB.NummerSectionC ,
    VaterSectionA = SUB.VaterSectionA , VaterSectionB = SUB.VaterSectionB , VaterSectionC = SUB.VaterSectionC
FROM @ProductRef T
JOIN 
(
    SELECT  
        t.ID, 
        t.SrcNummer,
        SUBSTRING (t.SrcNummer,0,s.NumFirstSeparator) AS NummerSectionA,
        SUBSTRING (t.SrcNummer,s.NumFirstSeparator+1,(s.NumSecondSeparator-s.NumFirstSeparator-1) ) AS NummerSectionB,
        RIGHT (t.SrcNummer,(LEN(t.SrcNummer)-s.NumSecondSeparator)) AS NummerSectionC,
        t.SrcVater,
        SUBSTRING (t.SrcVater,0,s.NumFirstSeparator) AS VaterSectionA,
        SUBSTRING (t.SrcVater,s.NumFirstSeparator+1,(s.NumSecondSeparator-s.NumFirstSeparator-1) ) AS VaterSectionB,
        RIGHT (t.SrcVater,(LEN(t.SrcVater)-s.NumSecondSeparator)) AS VaterSectionC
    FROM @ProductRef t
    JOIN SeparatorPosition s
    ON t.ID = s.ID  
) SUB
ON T.ID = SUB.ID 

Then you only work on the correct product ID section.

Answer 2

To answer your questions: yes, your solution works, and yes, there's something to make it bullet-proof. Make it bullet proof and reversible is what I suggest to do below. You will sleep better if you know you can A. Answer any question of angry people who ask you "what did you do to my product table" B. Know you can reverse any change you've made to this table (without restoring a backup), including mistakes of others (like wrong instructions).

So if you really have to be 100% confident of the output, I would not run it in one go. I suggest to prepare the queries in a separate table, then run the queries in a loop in dynamic SQL.

It is a little bit cumbersome but you can do it like this: create a dedicated table with the columns you need (like batch_id, insert_date, etc.) and a column named execute_query NVARCHAR(MAX). Then load your table by running a select distinct of the section you need to replace in your source table (using CHARINDEX to locate the second dot - make your CHARINDEX start from the CHARINDEX of the first dot+1 to get the second dot).

In other words: you prepare all the queries (like the ones in your examples) one by one and store them in a table.

If you want to be totally safe, the update queries can include a where source table_id between n and n' (which you build with a GROUP BY on the source table). This will secure that you can track which records you have updated if you have to answer questions later.

Once this is done, you run a loop which executes each line one by one. The advantage of this approach is to keep track of your changes - you can also prepare the rollback query at the same time as you prepare the update query. Then you know you can safely revert all the changes you have ever made to your product table.

Never truncate that table, it is your audit table. If someone ask you what you did to the product catalogue you can answer any question, even 5 years from now.