Menu
Is it possible to do something along those lines:
DELETE TOP(1) m
FROM MyTable m
OUTPUT deleted.*
LEFT JOIN MyOtherTable ON MyOtherTable.SomeColumn=s.SomeColumn
I know this works:
DELETE TOP(1)
FROM MyTable
OUTPUT deleted.*
And this also works (although pointless):
DELETE TOP(1) m
FROM MyTable m
LEFT JOIN MyOtherTable ON m.SomeColumn=MyOtherTable.SomeColumn
247
Left Join Vs Other Joins The LEFT JOIN selects the common rows as well as all the remaining rows from the left table. Whereas the INNER JOIN selects only the common rows between two tables. The LEFT JOIN selects the common rows as well as all the remaining rows from the left table.
The OUTPUT clause has access to two temporary or in-memory SQL tables, called INSERTED and DELETED tables. These tables are populated when an INSERT/UPDATE/DELETE operation is done on a table. As a result, the OUTPUT clause can provide us the affected records by referencing these tables. So let's see how to do this.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
Move the OUTPUT clause to before the JOIN
DELETE TOP(1) m
OUTPUT deleted.*
FROM MyTable m
LEFT JOIN MyOtherTable ON MyOtherTable.SomeColumn=s.SomeColumn
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
