SQL : Deleting Data

I will add more sql related topics. How do we delete data?

::SQL Delete Statement
The standard statement has two parts "from" and  "where".
Example:
DELETE from dbo.mytabletodelete
WHERE recordno < 15;

This statement basically deletes records based on where clause.
 
::TRUNCATE
This statement deletes all rows from a table. It performs better than delete statement. Truncate resets identity value back to original seed. One good protection to delete all rows from a table is to set foreign keys on this table. Truncate statement does not work if the target table is referenced by a foreign key constraint.
Example:
Truncate Table dbo.mytableallrows;

USE AdventureWorks;

GO
--Copy some table to dummy table for testing
Select * into dbo.truncatetest
from HumanResources.JobCandidate;

SELECT COUNT(*) AS BeforeTruncateCount
FROM dbo.truncatetest;

TRUNCATE TABLE dbo.truncatetest;

SELECT COUNT(*) AS AfterTruncateCount
FROM dbo.truncatetest;

GO::DELETE With A  Join Operation
You may want to delete filtered records  from the join operation. You can join multiple tables and check columns from those tables.
 
DELETE FROM T1
from dbo.atable as T1
 join dbo.btable as T2
 on T1.rowid = T2.rowid
where T1.afield = "something" and T2.anotherfield = "another"

Wow, we delete records from table T1 based on two conditions. You can write complex queries to filter what to delete.

::Delete with Top option
You can specify how many records to delete. If you want to take top 10 of some ordered list, you can do that.
 USE AdventureWorks;

DELETE TOP (2) PERCENT
FROM Production.ProductInventory;

::SQL UPDATE Statement
It allows you to update a subset from a table. If we start with the simple example:

UPDATE dbo.Products
  Set price = price * 1.1
WHERE Color = "blue"

Update happens in one call, so you can easily assign one column to anotherr column.

Update Table1
 Set col1 = col1 + 5, col2 = col1 + 3;

if Col1 is 10 and Col2 is 15 before the assignment, their values will be 15 and 13 after the assignment.

You can Update rows based on a join. This is similar to DELETE operation.

::UPdate based on a Join

UPDATE PR

Set price = price * 1.1
from dbo.Products as PR
  join dbo.Orders as O
  on PR.productid = O.productid

WHERE PR.Color = "blue" and O.orderstatus = "incomplete"
 
::Here, we use Orders table to find records with specific orderstatus for an update operation.

::OUTPUT Clause in Update
--if you are curious to see the column values before the update and after the update, you can use OUTPUT clause to return them

UPDATE dbo.OrderDetails
    set priority = priority +1
OUTPUT
    inserted.productid, deleted.priority as oldp,
    inserted.priority as newp
where  productid > 91;

------The end---------Next---> MERGe operations--->