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.
تعليقات