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
|
1 |
Delete tableName where conditions |
|
1 |
Delete from tableName where conditions |
|
1 |
Delete tableName |
TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK
SQL Delete From Examples on Library Database
Sample 1: Delete the book where book_id equal 20
|
1 |
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
|
1 |
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
|
1 |
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
|
1 |
Delete from autors |
Not: AutorId resumes from where It left off
Method 2
|
1 |
Truncate table autors |
Not: AutorId starts over
Sample 5: Delete the borrows of the student named John
|
1 |
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
|
1 |
Delete from students where student_id not in (Select student_id from borrows) |
Result Of Query: 1 row(s) affected.