mysql   delete  statement

the delete statement in MySQL is used to delete records in a table.

the mysql delete command allows you to remove records from not only one table but also multiple tables using a single delete statement.

mysql delete syntax

                delete     from    table_name

                where     condition ;

note : the where clause specifies which record should be deleted.if you omit the where clause, all records will be deleted.


database table use with delete command

below is a selection from the "user" table:

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
2shyamdhakashyamkumar12@gmail.comjaipur
3ankitnehraankit11@gmail.comuae

MySQL DELETE command  example

we wish to delete the user "sandeep" and "ankit" from the 'user' table.

use the following syntax

        
            mysql>delete      from     user

                   where       firstname = 'sandeep'  or   firstname = 'ankit' ;

output :

useridfirstnamelastnameemailcity
2shyamdhakashyamkumar12@gmail.comjaipur

mysql  delete record from multiple tables

if you want to delete records from multiple tables, you use one of the following delete statement :

                delete     from    table_name1 , table_name2, ...
 
                using   table-references

                where     condition ;
        
              mysql>delete      from     user,  person
                   
                   using        user,  person
                    
                   where       firstname = 'sandeep'  or   firstname = 'ankit' ;

output :try it yourself, please first create the user and person tables.


delete all record from a table or multiple tables

if you want to delete all records in a table use this syntax :

                   delete      from     table_name ;
                   
                   or
                   
                   delete   *   from   table_name ;

you must put the delete command with where clause otherwise the MySQL delete command immediate deletion of all records in the table.

the MySQL provide a alternative approach here would be use the TRUNCATE command, which also delete all the records in a table.The following command is equivalent to the delete command:-

   mysql> TRUNcate  Table  table_name

if you want to empty a table then the truncate command is faster then the delete, because truncate drops the table and regenerates it, while delete deletes records without making any attempt to modify the table itself.