You are here
Home > Sql Server > SQL Query (DELETE Statement)

SQL Query (DELETE Statement)

SQL Query

Delete Statement:

  • Delete statement is used to delete data from table.
  • Delete statement without where clause affects all rows of table.
  • Syntax of Delete statement:
  • delete from <table_name>
  • where condition;
  • Delete can be rolled back.
  • Delete tables can have triggers.
  • Delete is DML command.
  • It does not reset identity of the table.
  • Database Table: customer

Id

Name City
1 Anil

Anand

2

Smita V.V.Nagar
3 Rohan

Baroda

4

Samar Ahmedabad
5 Juhi

Anand

6

Jiya Borsad
  • Example:
  • delete from customer where Id=6;
  • One row will be deleted. Select data from customer table.
  • select * from customer;
  • Output:

Id

Name City
1 Anil

Anand

2

Smita V.V.Nagar
3 Rohan

Baroda

4

Samar Ahmedabad
5 Juhi

Anand

 

  • You can have more than one condition in delete statement.
  • 1) AND condition
  • 2) OR condition
  • Database Table: customer1

Id

Name City
1 Anil

Anand

2

Smita V.V.Nagar
3 Rohan

Baroda

4

Samar Ahmedabad
5 Juhi

Anand

  • Example:
  • delete from customer1
  • where Id=5
  • and Name< >’ Juhi’;
  • One row will be deleted. Select data from customer1 table.
  • select * from customer1;
  • Output:

Id

Name City
1 Anil

Anand

2

Smita V.V.Nagar
3 Rohan

Baroda

4

Samar Ahmedabad

 

  • In delete statement we use subquery as the condition.
  • Database Table: information_details

Id

Info_name City
1 Atul

V.V.Nagar

2

Piya Ahmedabad
3 Rohit

Baroda

4

Sita Surat
5 Ram

Baroda

 

  • Database Table: region

Region_name

Info_name
East

Anil

East

Gita

West

Rohan

West

Samar
North

Ram

North

Tina
South

Tom

South

Shiva

 

  • Example:
  • We want to remove all Info_name in the north region from information_details table.
  • We use following statements:
  • delete from information_details
  • where  Info_name in
  • (select Info_name from region where Region_name=’North’);
  • One row will be deleted. Select data from information_details table.
  • Select * from information_details;
  • Output:

Id

Info_name City
1 Atul

V.V.Nagar

2

Piya Ahmedabad
3 Rohit

Baroda

4

Sita Surat

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

One thought on “SQL Query (DELETE Statement)

  1. Hello there, I found your site via Google at the same time as searching for a comparable subject, your
    web site got here up, it appears to be like great.

    I’ve bookmarked it in my google bookmarks.
    Hi there, simply turned into alert to your blog through Google,
    and found that it’s truly informative. I’m gonna be careful for brussels.
    I’ll appreciate for those who proceed this in future. Numerous other
    folks shall be benefited out of your writing. Cheers!

Leave a Reply

Top