0

I am combining two databases of financial transactions. In one of the tables there is a field used to represents the allocation of the transaction with another record in the same table. It uses the Record ID and Amount to reference the corresponding record. When combining the data from the two table of the two databases, the Record ID will change. How do I change all the Record IDs within this field, from the old ID to the new corresponding ID? Is there a way. The RecordID is the "I=" values that needs to be replaced with the NewRecordID.

In other words how do I for example, replace the value I=53983 with I=3 everywhere in the Allocation field and the same with all the other "I=" values?

NewID    OldID    Allocation
1        53033    I=53983;A=260.28
2        53038    I=53983;A=2922.59|I=54420;A=2194.19
3        53983    I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59
4        54261    I=53983;A=62.93
5        54420    I=53038;A=2194.19
Improve this question
1
  • I corrected it, sorry about that. I originally copied it from notepad but the tabs and spacing didn't work so well. I added dots between the columns in order to get the column names and values inline. Commented Jul 11, 2018 at 11:54

3 Answers 3

Reset to default
1

Normally I would not suggest using a loop like this but I can't think of a cleaner way. 

-- Your table
DECLARE @table TABLE ([newid] INT, oldId INT, Allocation VARCHAR(8000));
INSERT @table
([newid],oldId,Allocation)
VALUES 
(1,53033,'I=53983;A=260.28'),
(2,53038,'I=53983;A=2922.59|I=54420;A=2194.19'),
(3,53983,'I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59'),
(4,54261,'I=53983;A=62.93'),
(5,54420,'I=53038;A=2194.19');

-- table to collect all the ids
DECLARE @ids TABLE (id INT);
INSERT @ids(id)
SELECT [newid] 
FROM @table;

-- routine to perform the update
DECLARE @id INT = 1;
WHILE @id <= (SELECT COUNT(*) FROM @table)
BEGIN
  UPDATE @table 
         SET Allocation = REPLACE(t.Allocation,f.old,'I='+CAST(@id AS VARCHAR(10))+';')
  FROM @table t
  CROSS APPLY (VALUES 
  ('I='+CAST(
    (SELECT t.oldId
     FROM @table t
     WHERE t.newid = @id)
    AS VARCHAR(10))+';')) f(old)
  WHERE CHARINDEX(f.old, t.Allocation) > 0;

  SET @id += 1;
END

SELECT * FROM @table;

Returns

newid       oldId       Allocation
----------- ----------- ------------------------------------------
1           53033       I=3;A=260.28
2           53038       I=3;A=2922.59|I=5;A=2194.19
3           53983       I=1;A=260.28|I=4;A=62.93|I=2;A=2922.59
4           54261       I=3;A=62.93
5           54420       I=2;A=2194.19

Note that my solution assumes that the newId value is a sequential set of numbers beginning with 1. You could easily tweak this if that's not the case.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I borrowed your table variable, thx for this and an upvote from my side.
You should avoid using loops in SQL as much as possible. SQL works best on a set based approach (manipulation sets or rows) . Using loops means going RBAR, and you really don't want to do that.
@zohar Iets see your set-based solution cursor-free, loop-free, rCTE-free way of solving this. I'll be waiting :)
@AlanBurstein use a string splitting function like Jeff Moden's DelimitedSplit8K (you don't want the built in string_split since you need to keep the order of the items as they where in the string) and you just use a cross apply to get the newId, oldId, and all Allocation values. Insert that result to a temporary table, do a self join update, and then re-generate the strings and update the original table. Shnugo's answer shows a way to do that with an XML based string splitter, Do we really need another answer like that?
Well done @ZoharPeled your solution is excellent. I was short on time and could not come up with a better, set-based solution than what I posted (note that I posted my solution 4 hours before anyone else). I would add that, what you posted is notably faster when run with a parallel execution plan but does not get one (on my system anyhow) unless you force a parallel plan using traceflag 8649 or make_parallel() by Adam Machanic.
|
1

As you were told already, the table's design is bad. With a proper design you would not have to solve this problem.

The following approach will use some replacements in order to get the concatenated string (an awful idea!) as structured XML. The result of the CTE RowWise should be enough for you in order to repair the bad design.

If you really need to stay with this, the final SELECT will return all your data re-organized:

DECLARE @table TABLE ([newid] INT, oldId INT, Allocation VARCHAR(8000));
INSERT @table
([newid],oldId,Allocation)
VALUES 
(1,53033,'I=53983;A=260.28'),
(2,53038,'I=53983;A=2922.59|I=54420;A=2194.19'),
(3,53983,'I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59'),
(4,54261,'I=53983;A=62.93'),
(5,54420,'I=53038;A=2194.19');


WITH Splitted AS
(
    SELECT * 
          ,CAST('<x><y type="' + REPLACE(REPLACE(REPLACE(Allocation,'=','">'),';','</y><y type="'),'|','</y></x><x><y type="') + '</y></x>' AS XML) AS TheXml
    FROm @table
)
,RowWise AS
(
    SELECT Splitted.oldId
          ,x.value('(y[@type="I"]/text())[1]','int') AS I
          ,x.value('(y[@type="A"]/text())[1]','decimal(10,4)') AS A
    FROM Splitted
    CROSS APPLY TheXml.nodes('/x') A(x)
)
SELECT t1.[newid],t1.oldId
      ,STUFF(
      (
        SELECT CONCAT('|','I=',t3.[newid],';A=',t2.A)
        FROM RowWise t2
        INNER JOIN @table t3 ON t2.I=t3.[oldId]
        WHERE t2.oldId =t1.oldId
        FOR XML PATH('')
       ),1,1,'')
FROM @table AS t1;

The replacements and the cast to XML will return something like this:

<x>
  <y type="I">53983</y>
  <y type="A">2922.59</y>
</x>
<x>
  <y type="I">54420</y>
  <y type="A">2194.19</y>
</x>
Improve this answer

3 Comments

Upvoted for not being as lazy as I was and posting the string splitter solution :-)
This is currently the best answer here. I wonder why it was downvoted.
@ZoharPeled Sometimes people just don't understand a good answer :-) Thx for your support!
0

Assuming that's the data in your table, you could do something as simple as this:

UPDATE MyTable
SET Allocation = REPLACE(Allocation, 
                         'I='+ CAST(OldID as varchar(11)) + ';', 
                         'I='+ CAST(NewID as varchar(11)) + ';')

However, please note it's a very bad practice to keep more then one data entity in a single column.
If possible, you better normalize your database - create a new table for Allocations, that contains columns for all the data parts currently in the column, and have a foreign key from it referencing the existing table.

For further reading: read the SO post Is storing a delimited list in a database column really that bad? and it's answers, where you will see a lot of reasons why the answer to this question is Absolutely yes!

Improve this answer

4 Comments

This would not work because the newId needs to be applied to ALL of the rows that contain the oldId (not just the current row)
Yes, I didn't think about that. Anyway the correct solution is still to normalize the database.
Thank you Zohar and Alan for the replies. I fully understand that it is a bad practice as you mentioned above. The database is one on Sage's Financial Software products. That's the way they wrote it and that how the front end product uses the data. I am merely trying to consolidate the data from two databases into one. But thank you very much.
Gotta love them vendor databases!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.