delete using inner join

Question

I have the below 4 tables. I tried to do some PoC (proof of concept) on delete with join query. So I tried to delete the rows in table 't1' and 'mytable' based on the join query on 'table1' and 'table2'. The delete query with 't1' and 'mytable' are both showing unusual behaviours.
Below is the query and table definition for the four tables:

--query starts here


DELETE mytable
FROM Table2 t1
inner join
Table2 t2 on t1.Col1=t2.col1        -- the query deletes all the rows from mytable

DELETE t1
FROM Table1
inner join
Table2 on table1.Col1=table2.col1   -- the query deletes all the rows from t1

Answer 1

You're not giving a join predicate for the target table so your queries are interpreted as CROSS JOINs.

This:

DELETE mytable
FROM Table2 t1
inner join
Table2 t2 on t1.Col1=t2.col1

Is equivalent to this (will have same execution plan):

DELETE mytable
FROM mytable
cross join (Table2 t1
    inner join
    Table2 t2 on t1.Col1=t2.col1)

And that's logically equivalent to this:

DELETE mytable
WHERE EXISTS (SELECT 1 FROM Table2 t1
    inner join
    Table2 t2 on t1.Col1=t2.col1)

Which, if that subquery has at least one row is logically equivalent to this:

DELETE mytable

You're suppose to reference the object you're deleting from in the join.

Like:

DELETE mytable
FROM mytable
INNER JOIN Table2 t1
    ON ????
inner join Table2 t2 
    on t1.Col1=t2.col1

Or, if you're using an alias:

DELETE m
FROM mytable m
INNER JOIN Table2 t1
    ON ????
inner join Table2 t2 
    on t1.Col1=t2.col1

Answer 2

This is the query you are looking for. This query deletes the common data from table1 and table2 in mytable and t1.

   DELETE mytable
    WHERE COL1 IN (SELECT T1.COL1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T2.COL1=T1.COL1)

 DELETE T1 
    WHERE COL1 IN (SELECT T1.COL1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T2.COL1=T1.COL1)