SQL連接兩個表并根據現有值插入/洗掉

我是 SQL 的新手并尋求幫助。我有兩個表結構如下：

桌子：T_Schools

ID      |   SchoolID        |  ChildID
-------- ------------------- -----------
1       |   School1         |  Child1
2       |   School2         |  Child2
3       |   School3         |  Child3
4       |   School4         |  Child4
7       |   School5         |  Child5
8       |   School6         |  Child6
9       |   School7         |  NULL
5       |   School8         |  NULL
6       |   School9         |  NULL
10      |   School10        |  Child10

桌子：T_Children

ChildID | ChildName 
-------- --------------
Child1  |   
Child2  |
Child3  |
Child4  |
Child5  |
Child6  |
Child7  |
Child8  |
Child9  |
Child10 |

我有兩種情況：

場景#1：子加法

如果ChildID在表中添加了一個新T_children值，而相應的值不存在T_Schools，則ChildID需要將這些值添加到第一個可用值SchoolID中。

預期結果：T_Schools

9       |   School7         |  Child7
5       |   School8         |  Child8
6       |   School9         |  Child9 

情景#2：兒童搬家

如果 aChildID從T_children表中洗掉，則相應ChildID的 fromT_Schools應設為 NULL，并且應使這些SchoolID's 可用于添加新ChildID的 's。

例如：如果ChildID = Child3從 中洗掉T_Children，那么T_Schools應該是這樣的：

3       |   School3         |  NULL

對于場景 #1，我嘗試了此處顯示的查詢，但它似乎沒有回傳所需的結果：

UPDATE M
SET M.ChildID = N.ChildID
FROM T_Schools M 
LEFT JOIN T_Children N ON M.ChildID <> N.ChildID  
WHERE M.ChildID IS NULL

任何幫助或建議將不勝感激。

提前致謝

uj5u.com熱心網友回復：

似乎插入，你只需要抓住第一個可用的School行。

所以給定兩個引數@ChildID和@ChildName：

BEGIN TRAN;

INSERT T_Children (ChildID, ChildName)
VALUES (@ChildID, @ChildName);

UPDATE TOP (1) T_Schools
SET ChildID = @ChildID
WHERE ChildID IS NULL;

COMMIT;

對于洗掉，反向執行相同操作

BEGIN TRAN;

UPDATE T_Schools
SET ChildID = NULL
WHERE ChildID = @ChildID;

DELETE T_Children
WHERE ChildID = @ChildID;

COMMIT;

您也可以使用表值引數來執行此操作。例如，如果您有一個包含列的 TVP，ChildID, ChildName您可以執行以下操作：

BEGIN TRAN;

INSERT T_Children (ChildID, ChildName)
SELECT t.ChildID, t.ChildName
FROM @tvp t;

UPDATE s
SET ChildID = t.ChildID
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T_Schools
    WHERE ChildID IS NULL
) s
JOIN (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM @tvp
) t ON t.rn = s.rn;

COMMIT;

對于洗掉：

BEGIN TRAN;

UPDATE s
SET ChildID = NULL
FROM T_Schools s
JOIN @tvp t ON t.ChildID = s.ChildID;

DELETE c
FROM c T_Children
JOIN @tvp t ON t.ChildID = c.ChildID;

COMMIT;

最后一個選項，雖然我建議盡可能避免它，是使用觸發器，并依靠級聯外鍵進行洗掉：

ALTER TABLE T_Schools
  ADD CONSTRAINT SchoolChild
    FOREIGN KEY (ChildID) REFERENCES T_Children (ChildID);

CREATE TRIGGER UpdateSchool ON T_Children
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;

UPDATE s
SET ChildID = t.ChildID
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM T_Schools
    WHERE ChildID IS NULL
) s
JOIN (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM inserted
) t ON t.rn = s.rn;

標籤：sqlsql服务器数据库加入

上一篇：有條件地向Spark資料框添加列

下一篇：如果上一步使用groupby子句和連接“完成”，則SQL查詢以獲取下一步的流程