CODEWITHSUNDEEP
sqlsql-servert-sql
grayson
grayson

Reputation: 1037

Update one table with the results from another

I have a TABLE (Classes_Teachers) with 3 columns (classID - INT, teacherID INT , isActive BOOLEAN).

I read the contents of a new file and create a table (StudentsTemp) with the same columns but with the latest Data and want to "merge" the two.

I can do this easily using EXCEPT AND JOIN, however this will delete any rows in Students that don't exist in StudentsTemp, however I need to be able to just set isActive to false as I have to keep the data for report purposes.

This is a "JOIN" table for a many to many relationship and so it does not have a single primary key.

So for example

STUDENTS:

+---------------------------------+
| ClassID   TeacherID   isActive  |
+---------------------------------+
| 1         2           1         |
| 1         12          1         |
| 1         13          0         |
| 5         10          1         |
+---------------------------------+

STUDENTS TEMP

+---------------------------------+
| ClassID   TeacherID   isActive  |
+---------------------------------+
| 1         2           1         |
| 1         13          1         |
| 1         6           1         |
+---------------------------------+

In this scenario Class 5 is no longer taught by Teacher 10 and so this entry would normally be been "deleted", but I only want to change "isActive" to 0

Class 1 is also no longer taught by Teacher 12 so this should change isActive to 0.

Class 1 is now taught instead by Teacher 13; ie is now "active" so this changes isActive to 1

Finally, there is a wholly new Teacher (id = 6) who teaches classID of 1 so this inserts the row as is.

I need STUDENTS to end up like this:

    +---------------------------------+
    | ClassID   TeacherID   isActive  |
    +---------------------------------+
    | 1         2           1         |
    | 1         12          0         |
    | 1         13          1         |
    | 5         10          0         |
    | 1         6           1         |
    +---------------------------------+

I can do this by creating 3 temporary tables and using EXCEPT AND JOIN to populate those tables with the "deletions", "changes" and "insertions" and then running UPDATE AND INSERT on them, but I wondered if there was an easier way to do this without having to create 3 extra temporary tables.

Upvotes: 0

Views: 86

Answers (3)

Jon C
Jon C

Reputation: 664

Try something like this:

MERGE Students AS T
USING Students_Temp AS S
ON (T.ClassId = S.ClassID AND T.TeacherID = S.TeacherID) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(ClassID, TeacherID, IsActive) VALUES(S.ClassID, S.TeacherID, S.IsActive)
WHEN NOT MATCHED BY SOURCE
    THEN UPDATE SET T.IsActive = 0
WHEN MATCHED 
    THEN UPDATE SET T.IsActive = S.IsActive

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

First, insert new teacherID and classID combination. Then update rows from Students table.

CREATE TABLE #Students(
    ClassID     INT,
    TeachedID   INT,
    isActive    BIT
)
CREATE TABLE #StudentsTemp(
    ClassID     INT,
    TeachedID   INT,
    isActive    BIT
)
INSERT INTO #Students VALUES
(1, 2, 1), (1, 12, 1), (1, 13, 0), (5, 10, 1);
INSERT INTO #StudentsTemp VALUES
(1, 2, 1), (1, 13, 1), (1, 6, 1);


UPDATE s
    SET s.isActive = ISNULL(t.isActive, 0)
FROM #Students s
LEFT JOIN #StudentsTemp t
    ON t.ClassID = s.ClassID
    AND t.TeachedID = s.TeachedID

INSERT INTO #Students
SELECT
    ClassID,
    TeachedID,
    isActive
FROM #StudentsTemp t
WHERE
    NOT EXISTS(
        SELECT 1
        FROM #Students
        WHERE
            ClassID = t.ClassID
            AND TeachedID = t.TeachedID
    )


SELECT * FROM #Students

DROP TABLE #Students
DROP TABLE #StudentsTemp

RESULT

ClassID     TeachedID   isActive
----------- ----------- --------
1           2           1
1           12          0
1           13          1
5           10          0
1           6           1

Upvotes: 1

zukanta
zukanta

Reputation: 2693

Have you tried using a Merge statement?

https://msdn.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 1

Related Questions