The Sql Delete Statement
- The Sql Delete Statement is used to remove records from tables.
- If we don’t write conditions, the tables all records are removed
- It’s the one of DML(Data Manupulation Language) codes
The Sql Delete Syntax
Delete tableName where conditions
Delete from tableName where conditions
Not: We can use all condition types where used on the select where clause:
SQL Delete From Examples on Library Database
Sample 1: Delete the book where book_id equal 20
Delete from books where bookId = 20
Result Of Query:1 row(s) affected.
Not: When we execute the query we can take the error “The DELETE statement conflicted with the REFERENCE constraint FK——“.If the tables have relations then we take the error. We must delete the book’s borrows first. Then we can delete the book.
Sample 2: Delete the students of 10A class
Delete from students where class = '10A'
Result Of Query: 23 row(s) affected.
Sample 3: Delete the Borrows where taken date between 10.10.2000 and 01.01.2002
Delete from borrows where takendate between '10.10.2016' and '01.01.2017'
Result Of Query: 1156 row(s) affected.
Sample 4: Delete all Autors
Delete from autors
Not: AutorId resumes from where It left off
Truncate table autors
Not: AutorId starts over
Sample 5: Delete the borrows of the student named John
Delete from borrows where studentId in (Select studentId from students where name = 'Tyler')
Result Of Query: 51 row(s) affected.
Sample 6: Delete students where has not read books
Delete from students where student_id not in (Select student_id from borrows)
Result Of Query: 1 row(s) affected.