DELETE from table1 the matching rows that are in table2 (no primary key in table1)-CodePudding

Table1:

StudentId (nvarchar(50))	Score (int)
abc	56
def	34
abc	95

Table2:

MemberId (nvarchar(50), no duplicates)	ComputerId (nvarchar(50))	ClassId (int)
abc	abc9119e-91d1-4205-8efe-ee87b7baa71	1
def	xyz9119e-91d1-4205-8efe-ee87b7b1111	2
efg	123-456-789	1
hij	xyz9119e-91d1-4205-8efe-ee87b7b1111	3

I want to delete the records from table1 that matches the following condition in table2, and insert the deleted into a different table (that has the same structure as table1):

Condition: table1.StudentId = table2.MemberId AND (for that record) table2.ComputerId is a uniqueidentifier AND ClassId = 1;

So in the above example, both the "abc" records will be deleted. Note that there is no primary key in table1.

My plan was to SELECT the correct records from table1, then put that SELECT in a DELETE, and then OUTPUT the deleted records into a different table (ignore the OUTPUT part in the below code).

My SELECT for the condition works fine and selects those 2 records, but when I put it in a DELETE, it deletes everything in table1.

My SELECT:

SELECT * FROM table1 A
WHERE EXISTS (SELECT *
                FROM table2 B
                WHERE A.StudentId = B.MemberId
                AND TRY_CONVERT(UNIQUEIDENTIFIER, B.ComputerId) IS NULL AND ClassId <> 1)

My DELETE:

DELETE FROM table1
WHERE EXISTS (SELECT * FROM table1 A
               WHERE EXISTS (SELECT *
                FROM table2 B
                WHERE A.StudentId = B.MemberId
                AND TRY_CONVERT(UNIQUEIDENTIFIER, B.ComputerId) IS NULL AND ClassId <> 1)
)

OUTPUT DELETED.*
INTO <table to insert into>;

CodePudding user response：

This should work:

DELETE a
-- SELECT *
FROM table1 A
WHERE EXISTS 
(
    SELECT *
    FROM table2 B
    WHERE A.StudentId = B.MemberId
    AND TRY_CONVERT(UNIQUEIDENTIFIER, B.ComputerId) IS NULL 
    AND ClassId <> 1
)

And probably would be better to use join instead of where exists like:

DELETE a
-- SELECT *
FROM table1 A
JOIN table2 B
on A.StudentId = B.MemberId
where TRY_CONVERT(UNIQUEIDENTIFIER, B.ComputerId) IS NULL 
AND ClassId <> 1

You can find more here on dba.stackexchange.

CodePudding user response：

This may also make sense to you:

DELETE FROM table1 WHERE StudentId IN
(
  SELECT memberId FROM table2 WHERE 
    TRY_CONVERT(UNIQUEIDENTIFIER, ComputerId) IS NULL AND ClassId <> 1
) 
OUTPUT DELETED.* INTO <table to insert into>;