score:1
Accepted answer
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
Read More
- Update one table with the results from another
- What is the quickest way to update each row in one table with a use-once value from another table?
- T-SQL: How do I get the rows from one table whose values completely match up with values in another table?
- Joining row from one table with the sum value from another table
- SQL Update with results from another Table
- Update multiple rows in one table with differing values from another
score:1
Have you tried using a Merge statement?
score:1
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
More questions
- SQL Server - Select Multiple Rows Data From One Table And Update Multiple Rows Data On Another Table With First Selected Data
- SQL Server: How can I copy data from one table to another table without the characters 'e+' being added to the results
- How to update a column of every row in a sql table with the value of that column from another specific row in the same table
- Using query results from one table in the SELECT list for another table
- How to update the a table with the values from table of another database
- Update and Replace Values in one Table Column with Values from another Table Column
- SQL update one single row table with data from another
- Update one table using the data from another table
- SQL Update One Table with Value from Another Table
- Read Top 10 ROWS from a table in one database and insert the concatenated results into a single column on another database
- Update SQL Server table with one time use values from another table
- UPDATE a field in one table with the SUM of another
- How can I parse data from one column in a table and put the result into another with SQL Server?
- Update multiple columns from one table with one column from another table
- Update columns from one table with appropriate column values from another table
- How to copy the table with values from one database to another database table?
- SQL Update from One Table to Another Based on a ID Match
- Update values from one column in same table to another in SQL Server
- SQL: Insert all records from one table to another table without specific the columns
- Stored Procedure to Insert Data from one table to another with same column names
- Copy data from one table onto another using SQL UPDATE statement
- TSQL: How can I update the value of an xml tag with the value of an xml tag from another related table?
- INSERT or UPDATE table from another table with composite primary key
- How to insert from one table into another with extra values in SQL?
More questions with similar tag
- More efficient way of querying for this data?
- SQL Server - updating distinct values
- Is there a way to use a table variable as the source for a joined update?
- One foreign key references multiple primary keys
- How do I get Percent of Total in SQL Rollup?
- how to record both success and fail in SQL Server Agent Job
- How to rename a MSSQL Database that has name "Database"?
- Why is UNION faster than an OR statement
- Is it possible to use a Case statement in a sql From clause
- How can I create a row for values that don't exist and fill the count with 0 values?
- Errors when adding SQL Server database to C#
- Check who performed insertion in table on MS SQL Server
- Can I use pivot to get counts for each value groups on MSSQL?
- Is it possible to change Script Task namespace identifier in SSIS?
- How to deserialize data records serialized with FOR XML AUTO in T-SQL?
- “Prevent saving changes that require the table to be re-created” Microsoft SQL Server
- How to Find Circular References in CTE between Parent/Child tables
- Table with JSON table inside
- Is it possible to search all databases in a SQL Server for a specific view? If yes how?
- Django connect to SQL Server - django/sql_server - pyodbc
- SQL RESTORE WITH RECOVERY; Hangs at 100%
- Bulk Update in C#
- Presenting hierarchical data with different conditions in SQL Server 2014
- Trying to Combine these Two Queries
- How do i get the names of all the tables inside a database?
- Identifying similar string values in two columns
- How does the LIKE operator work in SQL
- Select distinct values from one column and sum another column
- Insert a new row in an SQL table without assigning any values
- SQL How to transpose table from row to column
Source:
stackoverflow.com