top of page
Writer's picturecompnomics

Deleting Records in SQL


Introduction

Deleting records from a table in Oracle SQL is a common operation used to remove unwanted or obsolete data. In this blog post, we'll explore various delete statements, their syntax, and examples using our sample STUDENTS table.

Our Sample Table: STUDENTS

Column Name

Data Type

STUDENT_ID

NUMBER(10)

STUDENT_NAME

VARCHAR2(50)

ADDRESS

VARCHAR2(100)

PHONE

VARCHAR2(20)

CITY

VARCHAR2(50)

DISTRICT

VARCHAR2(50)

STATE

VARCHAR2(50)

Displaying All Records

Before we start deleting records, let's see the current state of our STUDENTS table:

SQL

SELECT * FROM STUDENTS;

This query will display all rows in the table.

Basic Delete Statement

The most fundamental delete statement is used to remove all rows from a table:

SQL

DELETE FROM table_name;
  • table_name: The name of the table you want to delete records from.

Example:

To delete all records from the STUDENTS table:

SQL

DELETE FROM STUDENTS;

Deleting Records Based on Conditions

You can use the WHERE clause to specify conditions that must be met for a row to be deleted. For example, you might want to delete only the records of students from a particular city:

SQL

DELETE FROM STUDENTS
WHERE CITY = 'Chandigarh';

Deleting a Specific Row

To delete a single row, you can use a WHERE clause that uniquely identifies the row. For instance, you could delete the record of a student with a specific STUDENT_ID:

SQL

DELETE FROM STUDENTS
WHERE STUDENT_ID = 101;

Deleting Multiple Rows

To delete multiple rows that meet a certain condition, use a WHERE clause that specifies the condition. For example, you could delete all records of students who have a phone number starting with '987':

SQL

DELETE FROM STUDENTS
WHERE PHONE LIKE '987%';

Precautions

  • Backup: Always create a backup of your data before performing deletions, especially if you're dealing with large datasets or critical information.

  • Testing: Test your delete statements on a test database or a subset of your data to ensure they produce the desired results.

  • Commit and Rollback: Use COMMIT to save your changes and ROLLBACK to undo them if necessary.

By understanding these delete statements and following best practices, you can effectively remove unwanted records from your Oracle tables.

19 views0 comments

Recent Posts

See All

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page