Vadim.K Vadim.K - 3 months ago 15
SQL Question

MS SQL Delete all unequal record

my problem is:
I have 2 tables (e.g.

T1
and
T2
) they both have field
Q_ID
. Table
T1
has some records in
Q_ID
column don't mach
T2
. My goal is to connect them with relation, so i want to delete all rows from
T2
, where
Q_ID
doesn't mach
Q_ID
from
T1
. I've tried to use inner join like this, but this script just deleted all records.

DELETE dst FROM HRM_MVC_DEV_Test.dbo.ProjectWorkers as dst
INNER JOIN HRM_MVC_DEV_Test.dbo.Project as src on dst.ProjectId<>src.ProjectId


I am using MS SQL server 2014

Answer

Maybe something like this:

CREATE TABLE T1 (ID INT, SomeValue VARCHAR(100));
CREATE TABLE T2 (ID INT, SomeValue VARCHAR(100));
INSERT INTO T1 VALUES
(1,'Row 1'),(2,'Row 2'),(3,'Row 3'),(4,'Row 4');
INSERT INTO T2 VALUES
(1,'Row 1'),(4,'Row 4');

DELETE FROM T1
WHERE T1.ID NOT IN(SELECT T2.ID FROM T2);

SELECT * FROM T1;
SELECT * FROM T2;