I will add more sql related topics. How do we delete data?
::SQL Delete Statement
The standard statement has two parts "from" and "where".
DELETE from dbo.mytabletodelete
WHERE recordno < 15;
This statement basically deletes records based on where clause.
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.
Truncate Table dbo.mytableallrows;
--Copy some table to dummy table for testing
Select * into dbo.truncatetest
SELECT COUNT(*) AS BeforeTruncateCount
TRUNCATE TABLE dbo.truncatetest;
SELECT COUNT(*) AS AfterTruncateCount
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.
DELETE TOP (2) PERCENT
::SQL UPDATE Statement
It allows you to update a subset from a table. If we start with the simple example:
Set price = price * 1.1
WHERE Color = "blue"
Update happens in one call, so you can easily assign one column to anotherr column.
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
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
set priority = priority +1
inserted.productid, deleted.priority as oldp,
inserted.priority as newp
where productid > 91;
------The end---------Next---> MERGe operations--->