Everything you need to get
your projects done.

SQL DELETE

DELETE permanently deletes rows from a table.

DELETE can delete one or more records in a table.

Use the WHERE clause to DELETE only specific records.

Example

#

Remove customer Elizabeth Lincoln who lives in Canada.

DELETE Customer
 WHERE FirstName = 'Elizabeth' 
   AND LastName = 'Lincoln'
   AND Country = 'Canada'

Be sure to include a WHERE clause or else all records are deleted from the table.

Syntax

DELETE syntax.

DELETE table-name 

DELETE syntax with WHERE.

DELETE table-name 
 WHERE condition

More Examples

SQL DELETE

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Remove all products.
DELETE Product
Result: 77 records deleted.

SQL DELETE WHERE

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Remove products over $50.
DELETE Product
 WHERE UnitPrice > 50
Result: 7 records deleted.

SQL DELETE single record

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Remove customer with Id = 21.
DELETE Customer
 WHERE Id = 21

This is a common scenario in which a single record is deleted.

Result: 1 record deleted.

Developers also Ask

#


Should you DELETE data from database?

This may seem like a strange question, but it is an important one.

First off, the DELETE command physically removes rows from a table.

This is called a hard-delete. Once deleted, the data cannot be recovered.

The opposite is a soft-delete in which a row is flagged as deleted but it is not physically deleted. This is accomplished with a BIT column named 'deleted'.

The advantage is that deleted data is recoverable.

The disadvantage is that queries must include a check for the deleted flag.

Another in-between model copies the deleted rows to a backup/recovery table before being deleted. These rows can then be recovered if necessary. The backup operation can be implemented quite elegantly with triggers.

Why is SQL DELETE so slow?

Deleting a single row with DELETE is generally fast enough.

However, deleting multiple rows can be slow because it is a transacted operation.

This means that all row are written to a SQL Server transaction log before the rows are actually deleted.

How can I speed up SQL DELETE?

To delete all rows in a table use TRUNCATE rather than DELETE.

To delete most rows in a table use SELECT INTO to save the data that needs to be preserved. Then use TRUNCATE to empty the table. Finally, move the saved data back to the original table.

To delete a smaller subset of rows in a table you can DELETE rows in chunks (groups of rows), so that the transactions are kept at a manageable size.,

With all these operations consider temporarily removing indexes and constraints on the table. Then execute the DELETE procedure. When completed re-apply the indexes and constraints.

Before applying any of the above be sure to research and test which approach is best for your situation.

Why is DELETE slower than TRUNCATE?

DELETE is transacted, meaning each row is logged before deletion.

TRUNCATE is not transacted and therefore much faster.

Even with large tables, TRUNCATE can be very fast.

Do we need a transaction for a SQL DELETE?

Yes. All DELETE operations are automatically transacted.

This is true, whether deleting 1 or a 10,000 rows.

These operations are transacted because if anything goes wrong, the changes can be rolled back (undone), and data integrity is maintained.

Can we rollback after DELETE?

DELETE operations can be rolled back (undone).

Use BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN to manage SQL transactions.


You may also like




Guides


vsn 3.1