Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Output clause with left join, how to?

Tags:

sql

sql-server

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
like image 247
Benoittr Avatar asked Jun 07 '11 14:06

Benoittr


People also ask

How do you use LEFT join?

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.

How do I do an output statement in SQL?

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.

How does a left join work in SQL?

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.


1 Answers

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 
like image 116
Duncan Howe Avatar answered Sep 28 '22 18:09

Duncan Howe