We often need to delete data from table based on JOIN.  In that case, what we do is, try to execute SELECT query with JOIN, we find and confirm the records we want to delete and then remove SELECT statement and write DELETE instead. But this will not work in case of JOIN. We need to give table alias explicitly to tell SQL Server Engine that what table we need to include in delete. Have a look at following case.

--create table1 for demo

create table Orders1

(

      OrderID INT not null constraint pk_ordid primary key,

      orderdate datetime

)

--create table2 for demo

create table orderDetails1

(

OrderDetailsID int not null,

OrderID INT not null constraint fk_ordid references Orders1(OrderID)

)

--insert records

insert into orders1

select 1,getdate() union all

select 2,getdate() union all

select 3,getdate() union all

select 4,getdate()

insert into orderdetails1

select 1,3 union all

select 2,2 union all

select 3,3 union all

select 4,1 union all

select 5,2

GO

--try to select records which you want to delete

select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

order by o.orderid

--now try to delete records by removeing

--SELECT statement and put DELETE instead.

--it will not work, as SQL Engine will be confused,

--what to delete, orders1? or orderdetails1?

delete

--select o.OrderID,o.orderDate,od.OrderDetailsID

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

--order by o.orderid

--here is the solution, you have to specify

--I need to delete from od (OrderDetails)

delete od

from Orders1 o inner join orderdetails1 od

on o.orderid=od.orderid

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

I love to write an article about differences and comparison as it is very useful to make your concept clear and also helpful in interview as many interviewers would like know the differences and comparison from the candidate. It proves candidate’s knowledge and his concept about subject.

I have written many articles for differences and comparisons. I would like to list few of them for reference here before we move to our core subject of the article.

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server.html

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server_16.html

Difference between SET and SELECT statement
http://ritesh-a-shah.blogspot.com/2009/03/set-and-select-in-sql-server-2005.html

Difference between EXEC and sp_ExecuteSQL
http://ritesh-a-shah.blogspot.com/2009/03/spexecutesql-and-execexecute-in-sql.html

Difference between BULK INSERT and BULK COPY (BCP)
http://ritesh-a-shah.blogspot.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

And now I am going to describe difference between DELETE and TRUNCATE

First of all let me start by these command’s type. DELETE is a DML command whereas TRUNCATE is a DDL command.

When you use DELETE command, SQL Server engine first move all data to the Rollback tablespace and then delete from the table. In this case disk space will not be immediately release. In short DELETE is a logged transaction and it used to write everything in log whereas TRUNCATE is completely by pass the log system and remove data right from the table so it is faster than DELETE.

If you wish to DELETE data from table based on condition than I recommend using DELETE as TRUNCATE will empty your table. You won’t get a single row in your table whereas we can use WHERE condition with DELETE so that you can remove only those records which you don’t really need.

Since TRUCATE is by passing the log system, you will not get any trigger in action set on the table whereas you will get delete trigger in action when you use DELETE command.

TRUNCATE command will reset your IDENTITY key whereas DELETE command won’t do it for you. In this manner TRUNCATE = DROP TABLE + CREATE TABLE or TRUNCATE=DELETE TABLE + COMMIT TRAN.

Above differences are specific to Microsoft SQL Server but it more or less works same in ORACLE, PostgreSQL and mySQL.

Reference: Ritesh Shah