What does this query do which exists inside a stored procedure?-CodePudding

I am trying to understand a stored procedure and the data is being loaded from Table2 to Table1. All columns are the same in Table1 and Table2.

DELETE a FROM Table1 a JOIN (SELECT DISTINCT [Date] FROM Table2) b ON a.Date = b.Date;

CodePudding user response：

It deletes Table1 rows where Table1.Date appears on any row in Table2.Date; i.e.

if Table1 has

Date
2022-01-01
2022-01-02
2022-01-03

and

Table2 has
Date
2022-01-01
2022-01-03

then if the DELETE statement is successful, Table1 will only have

Date
2022-01-02

and Table2 will not change.

CodePudding user response：

you have to use "exists" command like:

DELETE a FROM Table1 a 
where exists (select 0 from Table2 b where a.Date = b.Date);