SQL Join two Tables and Insert/ Remove depending on existing values-CodePudding

I am a newbie to SQL and seek help. I have two Table structures as below:

Table: 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

Table: T_Children

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

I have two scenarios:

Scenario #1: Child Addition

If a new ChildID is added in the T_children table and a corresponding value is not present in T_Schools, then those ChildID's need to be added into the first available SchoolID.

Expected Result: T_Schools

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

Scenario #2: Child Removal

If a ChildID is removed from the T_children table, then the corresponding ChildID's from T_Schools should be made NULL and those SchoolID's should be made available for adding new ChildID's.

For example: if ChildID = Child3 is removed from T_Children, then T_Schools should be like this:

3       |   School3         |  NULL

For scenario #1, I tried the query shown here, but it does not seem to return the required result:

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

Any help or advice would be appreciated.

Thanks in advance

CodePudding user response：

Seems like for inserting, you just need to grab the first available School row.

So given two parameters @ChildID and @ChildName:

BEGIN TRAN;

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

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

COMMIT;

And for deletion, do the same in reverse

BEGIN TRAN;

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

DELETE T_Children
WHERE ChildID = @ChildID;

COMMIT;

You can also do this using a Table Valued Parameter. For example, if you had a TVP containing the columns ChildID, ChildName you can do the following:

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;

And for deletion:

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;

One final option, although I advise to avoid it if possible, is to use a trigger, and rely on cascading foreign key for the delete:

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;