The DELETE statement in SQL

A short video covering the DELETE statement in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug                                         -- get code card up here
-- Douglas Kline
-- 4/8/2016
-- DELETE - how to remove records from  table  

USE Northwind

-- might want to review the SELECT statement
-- before using DELETE
-- in particular, review the WHERE clause,
-- because DELETE uses the WHERE clause

-- public service announcement :-)
/* ***WARNING!*** *****************

*INSERT, UPDATE and DELETE are *data modifying* commands

*INSERT, UPDATE and DELETE can do set-at-a-time operations

**this means that a seemingly simple statement
** can potentially change millions of records

* the database engine will not ask "Are you sure?"
* there is no simple "Undo"

* professionals normally don't, at the command line,
* execute INSERT, UPDATE, and DELETE commands
* on a production database

* treat these statements with respect!

*************************************/
-- the DELETE statement removes *entire* existing rows
-- if you want to remove *values* in a row, use UPDATE
-- (that is consider a change to a row)

-- let's look at the Orders table

SELECT *
FROM   Orders
ORDER BY orderID desc  -- note the descending order

-- now insert a bogus row to play with:
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Akron', 'VINET')

-- see what happened, and note the OrderID
SELECT *
FROM   Orders
ORDER BY orderID desc

-- OrderID=11109 for the new record

-- now that we have a row, here's a simple DELETE statement

DELETE
FROM    ORDERS
WHERE   orderID = 11109

-- NOTE: there is no "are you sure"
-- or "you are about to delete a million records, okay"
-- and SSMS doesn't have an "undo delete" button

-- now check the record
SELECT *
FROM   Orders
ORDER BY orderID desc

-- note that one record was affected
-- because the primary key was set to a single number

-- the most possible damage is a single record

-- let's put that record back in...
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Akron', 'VINET')

-- see what happened, and note the OrderID
SELECT *
FROM   Orders
ORDER BY orderID desc

-- OrderID=11110 for the new record

-- I like to do this before I do a DELETE:
SELECT *
FROM   Orders
WHERE  Shipcity='Akron'
 AND   CustomerID = 'VINET'

-- OK, that's the one (or more) records I want to get rid of
-- copy the statement down, and change to delete
DELETE
FROM   Orders
WHERE  Shipcity='Akron'
 AND   CustomerID = 'VINET'

-- and check the results
SELECT *
FROM   Orders
ORDER BY orderID desc

-- the general format for the DELETE statement is:
/*
DELETE
FROM    
WHERE   
*/

-- notice that the DELETE statement is very similar to SELECT
-- except you don't specify any columns...
-- because DELETE operates on *records*, not on columns

-- but what if you want to specify records to be deleted,
-- based on relationships with other tables,
-- in other words, a join

-- put the record back in:
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Akron', 'VINET')

-- and another
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Cleveland', 'RATTC')

-- and another
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('San Diego', 'BONAP')
-- consider this:

SELECT         *
FROM           Orders
   LEFT JOIN   [Order Details] ON Orders.OrderID = [Order Details].orderID
WHERE          [order details].orderID IS NULL

-- this is a classic "find unmatched"
-- in other words, this shows the orders 
-- without any lines on them - no related [order details] records
-- "widow" records because there are no "related" records,
--   and there *should* be, orders don't make sense without lines

-- btw,
-- you might want to check out my 
-- video on outer joins if you don't know this

-- so how do I delete widow records?
-- copy down, and change to DELETE...
DELETE
FROM           Orders
   LEFT JOIN   [Order Details] ON Orders.OrderID = [Order Details].orderID
WHERE          [order details].orderID IS NULL

-- darn, there's an error

-- this is because I can only DELETE from one table at a time
-- and I've got two tables specified
-- which table's records are to be deleted?

-- so here's the syntax:
DELETE         Orders  -- this is the changed part
FROM           Orders
   LEFT JOIN   [Order Details] ON Orders.OrderID = [Order Details].orderID
WHERE          [order details].orderID IS NULL

-- and check the results
SELECT *
FROM   Orders
ORDER BY orderID desc

-- so the more general syntax is
/*
DELETE      
FROM        
WHERE       
*/

-- where the table-expression normally includes
-- the table-name, with joins

-- so, do professionals normally *DELETE* data?
---
--
--
--
--
--
-- NO.
-- they normally *move* it somewhere,
-- after they make sure it made it to the new location safely
-- they remove it from where it was
-- and of course, they do a backup first
-- 
--
-- Database by Doug                                         
-- Douglas Kline
-- 4/8/2016
-- DELETE - how to remove records from  table