{"id":374,"date":"2019-10-14T17:46:47","date_gmt":"2019-10-14T10:46:47","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=374"},"modified":"2020-04-11T23:24:06","modified_gmt":"2020-04-11T16:24:06","slug":"mariadb-delete","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-delete\/","title":{"rendered":"MariaDB Delete"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB delete<\/code> statement to remove one or more rows from a table.<\/p>\n\n\n\n

Introduction to MariaDB delete statement<\/h2>\n\n\n\n

To delete one or more rows from a table, you use the delete<\/code> statement:<\/p>\n\n\n

delete<\/span> from<\/span> table_name\n[where<\/span> search_condition];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify the name of the table from which you want to remove data after the delete from<\/code> keywords.<\/li>
  • Second, specify which rows to delete by using a condition in a where<\/code> clause. The where<\/code> clause is optional. If the where<\/code> clause is available, the delete<\/code> statement only removes rows that cause the search_condition<\/code> to evaluate to true. The delete<\/code> statement will do nothing if no row from the table causes the search_condition<\/code> to evaluate to true. In case you omit the where<\/code> clause, the delete<\/code> statement will remove all rows from the table.<\/li><\/ul>\n\n\n\n
    In a relational database, a table is associated with other tables via foreign key constraints. If you delete rows from the parent table, the corresponding rows from the child tables are automatically deleted if the foreign key constraints use on delete cascade<\/code> action.<\/p>\n\n\n\n
    MariaDB delete statement examples<\/h2>\n\n\n\n
    We’ll use the contacts<\/code> table for the demonstration.<\/p>\n\n\n
    create<\/span> table<\/span> if<\/span> not<\/span> exists<\/span> contacts(\n    id<\/span> int<\/span> auto_increment,\n    first_name varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    last_name varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    full_name varchar<\/span>(101<\/span>) \n        as<\/span> (concat<\/span>(first_name, ' '<\/span>, last_name)) virtual<\/span>,\n    phone varchar<\/span>(100<\/span>),\n    contact_group varchar<\/span>(50<\/span>) default<\/span> 'General'<\/span>,\n    primary key<\/span>(id<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following shows the contents of the contacts<\/code> table:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"\"<\/figure>\n\n\n\n
    A) Deleting one row from a table example<\/h3>\n\n\n\n
    The following example uses the delete<\/code> statement to remove one row from a table:<\/p>\n\n\n
    delete<\/span> from<\/span> contacts\nwhere<\/span> id<\/span> = 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    MariaDB issued the following message:<\/p>\n\n\n
    Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The number of affected rows is 1. It means that the row with id 1 has been deleted successfully.<\/p>\n\n\n\n
    You can verify the deletion by querying data from the contacts<\/code> table:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts\nwhere<\/span> id<\/span> = 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returns no row.<\/p>\n\n\n\n
    B) Deleting multiple rows from a table example<\/h3>\n\n\n\n
    The following statement uses the delete<\/code> statement to delete contacts whose last name is 'Smith'<\/code> from the contacts<\/code> table:<\/p>\n\n\n
    delete<\/span> from<\/span> contacts\nwhere<\/span> last_name = 'Smith'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The number of affected rows is 2:<\/p>\n\n\n
    Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following query finds the contacts whose last name is 'Smith'<\/code>:<\/p>\n\n\n
    select<\/span> * from<\/span> contacts\nwhere<\/span> last_name = 'Smith'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The query returns no rows.<\/p>\n\n\n\n
    C) Deleting all rows from a table example<\/h3>\n\n\n\n
    The following example uses the delete<\/code> statement to remove all rows from the contacts<\/code> table:<\/p>\n\n\n
    delete<\/span> from<\/span> contacts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the delete<\/code> statement has no where<\/code> clause so it deletes all rows from the contacts<\/code> table.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t