How can I update strings within an SQL server table based on a query?

Question

I have two tables A and B. A has an Id and a string with some embedded information for some text and ids from a table C that is not shown

Aid| AString
 1   "<thing_5"><thing_6">"
 2   "<thing_5"><thing_6">"

 Bid|Cid|Aid
  1   5   1
  2   6   1
  3   5   2
  4   6   2

I realise this is an insane structure but that is life.

I need to update the strings within A so that instead of having the Cid they have the corresponding Bid (related by the Aid and Bid pairing)

Is this even something I should be thinking of doing in SQL... A has about 300 entries and B about 1200 so not something doing by hand

For clarity I wish for B to remain the same and A to finally look like this

Aid| AString
 1   "<thing_1"><thing_2">"
 2   "<thing_3"><thing_4">"

Answer 1

This script relies on generating dynamic SQL statements to update the table, then executes those statements.

• Taking into account that the cid's are within thing_ and ":
• First replaces the cid's using a placeholder ($$$$$$ in this case) to account for the fact that cid's and bid's may overlap (example, changing 3->2 and later 2->1)
• Then changes the placeholders to the proper bid

---

CREATE TABLE #a(aid INT,astr VARCHAR(MAX));
INSERT INTO #a(aid,astr)VALUES(1,'<thing_5"><thing_6">'),(2,'<thing_5"><thing_6">');

CREATE TABLE #rep(aid INT,bid INT,cid INT);
INSERT INTO #rep(bid,cid,aid)VALUES(5,6,1),(6,5,1),(3,5,2),(4,6,2);

DECLARE @cmd NVARCHAR(MAX)=(
    SELECT
        'UPDATE #a '+
        'SET astr=REPLACE(astr,''thing_'+CAST(r.cid AS VARCHAR(16))+'"'',''thing_$$$$$$'+CAST(r.cid AS VARCHAR(16))+'"'') '+
        'WHERE aid='+CAST(a.aid AS VARCHAR(16))+';'
    FROM
        (SELECT DISTINCT aid FROM #a AS a) AS a
        INNER JOIN #rep AS r ON
            r.aid=a.aid
    FOR 
        XML PATH('')
);
EXEC sp_executesql @cmd;

SET @cmd=(
    SELECT
        'UPDATE #a '+
        'SET astr=REPLACE(astr,''thing_$$$$$$'+CAST(r.cid AS VARCHAR(16))+'"'',''thing_'+CAST(r.bid AS VARCHAR(16))+'"'') '+
        'WHERE aid='+CAST(a.aid AS VARCHAR(16))+';'
    FROM
        (SELECT DISTINCT aid FROM #a AS a) AS a
        INNER JOIN #rep AS r ON
            r.aid=a.aid
    FOR 
        XML PATH('')
);
EXEC sp_executesql @cmd;

SELECT * FROM #a;

DROP TABLE #rep;
DROP TABLE #a;

Result is:

+-----+----------------------+
| aid |         astr         |
+-----+----------------------+
|   1 | <thing_6"><thing_5"> |
|   2 | <thing_3"><thing_4"> |
+-----+----------------------+