DELETE a row from table SQL query

The SQL DELETE STATEMENT

The DELETE statement is used to delete existing records in a table. The DELETE statement does not return any rows; that is, it will not generate a result set. Executing a DELETE statement can cause triggers to run that can cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in the referenced table are deleted, then it is common that rows in the referencing table would also have to be deleted to maintain referential integrity. We can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Example

Let us see all the records of STUDENTS_COPY table first.

SELECT * FROM STUDENTS_COPY;

The following SQL statement deletes the student   "Oliver Queen" from the STUDENTS_COPY  table.

DELETE FROM STUDENTS_COPY

WHERE ROLLNO=4;

COMMIT;

We can see below that the record for the Oliver Queen is not present in the table.

SELECT * FROM STUDENTS_COPY;

Example

We can delete all rows in a table without deleting the table, which means that the table structure, attributes, and indexes will be not altered.

DELETE FROM STUDENTS_COPY;

COMMIT;

SELECT * FROM STUDENTS_COPY;

Example using EXIST clause

When we want to delete records in one table based on values in another table. Since we cannot list more than one table in the FROM clause while performing a delete, there we can use the EXIST clause.

Now we will delete record from STUDENTS_COPY table based on condition met on MARKS table.

SELECT * FROM MARKS;

SELECT * FROM STUDENTS_COPY;

DELETE FROM STUDENTS_COPY

WHERE EXISTS

  (SELECT *

   FROM MARKS

   WHERE MARKS.ROLLNO = STUDENTS_COPY.ROLLNO

   AND MARKS.ROLLNO =12);

SELECT * FROM STUDENTS_COPY;

We can see that the record for "Leonard Snart" is removed from the STUDENTS_COPY table based on ROLLNO.  mentioned in where clause on MARKS table.