SQL Delete | Delete from Statement - sql - sql tutorial - learn sql




 delete from statment
  • The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
  • The DELETE FROM statement in SQL is used to remove records from a table.
  • DELETE FROM command cannot delete any rows of data that would violate

Syntax

  • The syntax for the DELETE FROM statement is as follows:
DELETE FROM "table_name"
WHERE "condition";
  • The WHERE clause is important here.
  • Without specifying a condition, all records from the table will be deleted.
  • "Condition" can be simple (such as "Sales > 500") or complex (such as from the result of a subquery).
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Examples

  • Two examples of how to use the DELETE FROM statement are shown below.

Example 1: DELETE FROM using a simple condition

  • We use the following table as the starting point.
  • Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
  • We decide not to keep any information on Los Angeles in this table. To accomplish this, we type the following SQL:
Ø	DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';
  • Now the table becomes,

Table Store_Information

Store_Name Sales Txn_Date
San Diego 250 Jan-07-1999
Boston 700 Jan-08-1999

Example 2: DELETE FROM using the results from a subquery

  • In Example 1, the criteria we use to determine which rows to delete is quite simple. We can also use a more complex condition. Below is an example where we use as the condition. Assume we have the following two tables:
  • Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
  • Table Geography
Region_Name Store_Name
East Boston
East New York
West Los Angeles
West San Diego
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
  • We want to remove data for all stores in the East region from Store_Information (assuming that a store is either in the East region or the West region-it cannot be in more than one region). We use the following SQL statement to accomplish this:
DELETE FROM Store_Information
WHERE Store_Name IN
(SELECT Store_Name FROM Geography
WHERE Region_Name = 'East');
  • Upon execution, the Store_Information table becomes,
  • If we leave out the clause in a DELETE FROM command, we will delete all rows from the table. Most times, this is not what we intend to do. To prevent this, it is a best practice in database management to always run the corresponding statement first to make sure the rows selected are the ones we intend to remove from the table. This can be done by replacing "DELETE" with "SELECT *".

Exercises

  • For the questions below, we use the following table as the starting point:

Table Clients

Customer_ID Last_Name First_Name City State Join_Date
2 Larry Larry Seattle WA Oct-15-2012
5 Aaron Wallace Denver CO Oct-18-2012
6 Jayson Fortran Raleigh NC Oct-24- 2012
12 jill Dobbs Buffalo NY Nov-15-2012
13 Lisa Yamaguchi San Diego CA Nov-15- 2012
20 Ally Smith Seattle WA Nov-25-2012
67 Teyu Lee Cupertino CA Jan-11-2013

Which of the following SQL statements is valid? (There may be more than one answer)

a) DELETE * FROM Clients WHERE State = 'CO'; 
b) DELETE FROM Clients WHERE State = 'CO'; 
c) DELETE FROM Clients HAVING State = 'CO'; 
d) DELETE FROM Clients WHERE Customer_ID < 10;
  • How many rows are deleted after the following SQL statement is executed?
  • DELETE FROM Clients WHERE State = 'CO';

Answer

1 row
  • What is the effect of the following SQL?
  • DELETE FROM Clients WHERE 1 = 1;

Answer

All rows are deleted from the table. This is because the condition 
in the WHERE clause is true for all rows.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Delete vs Truncate

Delete vs Truncate

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Delete from Statement