search
Search
Login
Math ML Join our weekly DS/ML newsletter
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
check_circle
Mark as learned
thumb_up
10
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

MySQL | DELETE

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Statements and Clauses
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags

MySQL's DELETE statement is used to delete existing records in a table.

WARNING

You need the DELETE privilege on a table to delete records from it.

Syntax

DELETE FROM table_name
WHERE condition;

Examples

Consider the following table about whether students hold a drivers license:

student_id

full_name

age

has_license

1

Sky Towner

17

0

2

Ben Davis

19

1

3

Travis Apple

18

0

4

Arthur David

16

0

5

Benjamin Town

17

1

The above sample table can be created using the code here.

Deleting a single record

To delete the record for 'Ben Davis' as he has now left the school:

DELETE FROM drivers_license
WHERE student_id = 2;
Query OK, 1 row affected (0.01 sec)

We can see that 'Ben Davis' has now been removed from drivers_license table:

SELECT *
FROM drivers_license;
+------------+---------------+------+-------------+
| student_id | full_name | age | has_license |
+------------+---------------+------+-------------+
| 1 | Sky Towner | 18 | 0 |
| 3 | Travis Apple | 18 | 0 |
| 4 | Arthur David | 16 | 0 |
| 5 | Benjamin Town | 17 | 1 |
+------------+---------------+------+-------------+
WARNING

Take care whenever you are performing DELETE on your table. If we forget to add the WHERE clause, all the records in the table will be deleted.

DELETE FROM drivers_license;

SELECT *
FROM drivers_license;
Empty set (0.01 sec)

Deleting multiple records

Consider the following table about whether students hold a drivers license:

student_id

full_name

age

has_license

1

Sky Towner

17

0

2

Ben Davis

19

1

3

Travis Apple

18

0

4

Arthur David

16

0

5

Benjamin Town

17

1

To delete records for students with student_id 1, 2 or 3:

DELETE FROM drivers_license
WHERE student_id IN (1, 2, 3);

SELECT *
FROM drivers_license;
+------------+---------------+------+-------------+
| student_id | full_name | age | has_license |
+------------+---------------+------+-------------+
| 4 | Arthur David | 16 | 0 |
| 5 | Benjamin Town | 17 | 1 |
+------------+---------------+------+-------------+
NOTE

Before using the DELETE statement, perform a SELECT statement with the same conditions to see what exactly you will be deleting. Moreover, whenever you are performing any large operation with your data, make sure to back it up first!

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...
thumb_up
10
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!