To remove records from a table we use the DELETE statement.
The DELETE statement deletes rows from a table, alias, or view,
or the underlying tables, aliases, or views from the specified fullselect.
Deleting a row from an alias deletes the row from the data source object
that the alias refers to.
Deleting a row from a view deletes the row from the table on which the
view is based if no INSTEAD OF trigger has been defined for the delete
operation on this view.
If such a trigger has been defined, the trigger will be executed instead.
There are two forms of this statement:
- The DELETE with search form is used to delete one or more
rows (optionally determined by a search condition). - The DELETE form with position is used to delete exactly one
row (determined by the current position of the cursor).
DELETE from student;
This statement displays a message indicating the number of records it has
deleted.
If you do not want to delete all the records, but only some, you must
indicate which one or which ones, for this, use the DELETE command
together with the WHERE clause with which you can establish the
condition that the records to be deleted must meet.
For example, you may want to delete that record whose name is “George”:
DELETE FROM student WHERE name=’George’;
If you request the deletion of a record that does not exist, that is, no
record meets the specified condition, no record will be deleted.
Note that if you don’t put a condition, all the records of the named
table are deleted.
For the example, you must create a table with the name ‘student’
CREATE TABLE `student` (`idStudent` int(11) DEFAULT NULL, `name`
varchar(50) DEFAULT NULL, `idSupervisor` int(11) DEFAULT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
And then, fill it with the following data:
INSERT INTO dbo.student (`idStudent`, `name`, `idSupervisor`)
VALUES
(1, ‘ANN’, 4),
(2, ‘GEORGE’, 4),
(3, ‘BEA’, 2),
(4, ‘CHRISTOPHER’, 3);
Now, if you want to delete the record that contains the name ‘GEORGE’,
you must write the following statement:
DELETE FROM dbo.student WHERE name=’George’;
Consider that if you do not delimit the statement with WHERE, you
are going to delete all the records of the table.
In general terms, a DELETE statement can be embedded in an
application program or issued using dynamic SQL statements.
This is an executable statement that can be dynamically prepared.
Lastly, this delete statement completes a trio with INSERT and UPDATE
for full control over the information stored in our databases.