Cacher is the code snippet organizer for pro developers

/*********************************
DELETE DATA
*********************************/

/*
DELETE deletes rows from a table. If a predicate is used, it only deletes those rows. If a predicate is not
used, it deletes all rows. BE CAREFUL WITH THIS!
*/
DELETE FROM products
WHERE productID = 2;

/*********************************
DELETE PERFORMANCE
*********************************/

/*
A DELETE statement is fully logged, and as a result, large deletes can take a very long time to complete,
and much longer to roll them back. This can also cause the transaction log to increase in size dramatically.
They can also result in lock escalation where an entire table gets locked from transactions.

To prevent this, it can be a good idea to break the delete up. You can do this with a DELETE statement using
a TOP option that limits the number of affected rows. In order to not have to manually run the statement
a number of times, you can set up a loop with a BREAK that breaks the loop if a certain row count threshold
is not met (meaning it's run out of room).
*/
WHILE 1 = 1 -- starts an infinite loop
BEGIN
	DELETE TOP(1000) FROM products

	IF @@rowcount < 1000 -- shows how many rows were affected by last statement. If less than 1,000, that means it has run out of rows.
		BREAK
	END
	
/*********************************
TRUNCATE TABLE
*********************************/

/*
TRUNCATE TABLE is an optimized statement that deletes all rows from the target table. It removes all rows
but leaves the table definition in place.

The differences between TRUNCATE TABLE and DELETE:

- TRUNCATE doesn't support a filter
- it uses an optimized logging mode and therefore is much faster than DELETE
- it resets the IDENTITY property on any IDENTITY columns, whereas DELETE does not reset the property
- you need to drop foreign keys, TRUNCATE the table, and then recreate the foreign keys (as opposed to
DELETE which allows you to remove the rows as long as no related rows are in the referencing table)
- it isn't usable if there are indexes on the table (you can DELETE if there is an index)
*/

TRUNCATE TABLE products;

/*********************************
DELETE BASED ON A JOIN
*********************************/

/*
Much like how you can UPDATE a table based on a JOIN, SQL Server also lets you DELETE based on a JOIN. The idea is 
to allow you to delete rows from one table based on the presence of related rows in other tables, with the 
ability to apply a filter predicate that is based on attributes in the related tables.

In the example below, it deletes in T_APPEAL that have a related row in TR_APPEAL_CATEGORY with a 
description like 'Annual Fund'.

Note that there are two FROM clauses, the first FROM which states from which table to delete the rows,
and then the normal FROM which is used for the JOIN.
*/

DELETE FROM a -- SELECT clause is replaced by DELETE clause
FROM T_APPEAL a -- the normal FROM which is used as the bassis for the JOIN
JOIN TR_APPEAL_CATEGORY b on a.appeal_no = b.appeal_no and b.description LIKE '%Annual Fund%

/*
You can optionally return to the user or INSERT INTO another table information from the DELETEd
rows by using OUPUT or OUTPUT...INTO respectively. See below for a full example with an OUTPUT.
*/

/**** CREATE TEMP BACKUP TABLE ****/
CREATE TABLE #km_attribute_delete_backup (keyword_no INT, customer_no INT, old_key_value VARCHAR(50))


/**** DELETE ROWS FROM TX_CUST_KEYWORD ****/

--delete from TX_CUST_KEYWORD where key_value 'Annual Fund', (~145) rows
DELETE a
OUTPUT deleted.keyword_no, deleted.customer_no, deleted.key_value INTO #km_attribute_delete_backup -- output deleted rows as backup
FROM TX_CUST_KEYWORD a
JOIN lt_manual_listing_attribute_removal_20190308 b on a.customer_no = b.customer_no and b.manual_update_attribute_type = 'AF'
WHERE a.keyword_no = 317 and a.key_value = 'AF'

--delete from TX_CUST_KEYWORD where key_value 'All' (~13 rows)
DELETE a
OUTPUT deleted.keyword_no, deleted.customer_no, deleted.key_value INTO #km_attribute_delete_backup -- output deleted rows as backup
FROM TX_CUST_KEYWORD a
JOIN lt_manual_listing_attribute_removal_20190308 b on a.customer_no = b.customer_no and b.manual_update_attribute_type = 'All'
WHERE a.keyword_no = 317 and a.key_value = 'All'

--delete from TX_CUST_KEYWORD where key_value 'Institute' (~13 rows)
DELETE a
OUTPUT deleted.keyword_no, deleted.customer_no, deleted.key_value INTO #km_attribute_delete_backup -- output deleted rows as backup
FROM TX_CUST_KEYWORD a
JOIN lt_manual_listing_attribute_removal_20190308 b on a.customer_no = b.customer_no and b.manual_update_attribute_type = 'Institute'
WHERE a.keyword_no = 317 and a.key_value = 'Institute'

--delete from TX_CUST_KEYWORD where key_value 'Annual Report' (~111 rows)
DELETE a
OUTPUT deleted.keyword_no, deleted.customer_no, deleted.key_value INTO #km_attribute_delete_backup -- output deleted rows as backup
FROM TX_CUST_KEYWORD a
JOIN lt_manual_listing_attribute_removal_20190308 b on a.customer_no = b.customer_no and b.manual_update_attribute_type = 'Annual Report'
WHERE a.keyword_no = 317 and a.key_value = 'Annual Report'


/**** CHECK AND SAVE BACKUP TABLE ****/
select * from #km_attribute_delete_backup