sql - 如何根据查询更新 SQL Server 表中的字符串？

Question

我有两个表A 和B。 A 有一个 Id 和一个字符串，其中包含某些文本的一些嵌入信息和表 C 中未显示的 id

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

我意识到这是一个疯狂的结构，但这就是生活。

我需要更新 A 中的字符串，以便它们具有相应的 Bid 而不是 Cid(由 Aid 和 Bid 配对)

这甚至是我应该考虑在 SQL 中做的事情吗...A 有大约 300 个条目而 B 大约有 1200 个所以不是手工做的事情

为了清楚起见，我希望 B 保持不变，而 A 最终看起来像这样

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

Answer 1

此脚本依靠生成动态 SQL 语句来更新表，然后执行这些语句。

• 考虑到 cid 在 thing_ 和 " 中:
• 首先使用占位符(在本例中为 $$$$$$)替换 cid，以说明 cid 和 bid 可能重叠(例如，更改 3->2 和后来的 2->1)
• 然后将占位符更改为正确的 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;

结果是:

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