MySQL

The DELETE statement in MySQL is used to remove one or more rows from a table. This operation is crucial when you need to remove outdated or incorrect records from your database. For instance, in a company’s database, you may want to delete an employee’s record when they leave the company or remove a branch that has been closed. The DELETE statement can be used in conjunction with conditions to target specific rows for deletion, ensuring that only the intended data is removed.

Example Scenario:

You have tables for company, employees, departments, and branches, and you need to delete specific records based on certain conditions, such as removing an employee who has resigned.

Steps to Use the DELETE Operation:

1. Basic Syntax of DELETE
   • The basic format of the DELETE statement is as follows:

     DELETE FROM table_name
     WHERE condition;
     

   • Explanation:
     • table_namerefers to the table from which you want to delete rows.
     • condition specifies which rows should be deleted. Without a condition, all rows in the table will be deleted.
2. Example 1: DELETE from employees Table
   • Suppose the employees table has the following structure:

     CREATE TABLE employees (
     employee_id INT AUTO_INCREMENT,
     first_name VARCHAR(50),
     last_name VARCHAR(50),
     department_id INT,
     salary DECIMAL(10, 2),
     hire_date DATE,
     PRIMARY KEY (employee_id)
     );
     

   • To delete an employee withemployee_id = 5,use the following statement:

     DELETE FROM employees
     WHERE employee_id = 5;
     

   • Explanation:
     • This deletes the employee with the ID 5 from the employeestable.
3. Example 2: DELETE with Multiple Conditions
   • You can use multiple conditions in the WHERE clause to target specific rows for deletion. For example, to delete an employee from the employees table who is in department 2and has a salary less than 50,000:

     DELETE FROM employees
     WHERE department_id = 2 AND salary < 50000;
     

   • Explanation:
     • This deletes all employees from department 2 who have a salary less than 50,000.
4. Example 3: DELETE from departments Table
   • Suppose thedepartments table has the following structure:

     CREATE TABLE departments (
     department_id INT AUTO_INCREMENT,
     department_name VARCHAR(50),
     branch_id INT,
     PRIMARY KEY (department_id)
     );
     

   • To delete a department withdepartment_id = 3:

     DELETE FROM departments
     WHERE department_id = 3;
     

   • Explanation:
     • This removes the department with the ID 3 from thedepartmentstable.
5. Using DELETE Without a WHERE Clause
   • If you use DELETE without a WHERE clause, it will delete all rows in the table:

     DELETE FROM employees;
     

   • Explanation:
     • This command deletes all records in the employeestable. Use this with caution as it cannot be undone without a backup.
6. Example 4: DELETE from branches Table
   • Suppose thebranchestable has the following structure:

     CREATE TABLE branches (
     branch_id INT AUTO_INCREMENT,
     branch_name VARCHAR(100),
     location VARCHAR(100),
     PRIMARY KEY (branch_id)
     );
     

   • To delete a branch located in 'New York':

     DELETE FROM branches
     WHERE location = 'New York';
     

   • Explanation:
     • This removes all branches that are located in New York.

Notes on Using the DELETE Operation:

1. Important!
   • If you omit the WHERE clause, all rows in the table will be deleted. Always ensure that you have a specific WHERE condition to avoid accidental data loss.
2. Foreign Key Constraints:
   • If your table has foreign key constraints, attempting to delete a row may result in an error if other rows in related tables reference the row you’re trying to delete. Use ON DELETE CASCADE to automatically delete related rows when a row is deleted
3. Use with Transaction Control:
   • It's recommended to use DELETE statements within transactions, especially when deleting multiple rows. This allows you to ROLLBACK in case something goes wrong:

      START TRANSACTION;
     DELETE FROM employees WHERE employee_id = 10;
     COMMIT;
     

4. Real-World Usage in a Company Database:
   • In a company database, you can use the DELETE operation to remove employees who have left the company, delete closed departments or branches, or remove other outdated information.
5. Safe Deletion Practices:
   • Before running a DELETE command, it’s often a good idea to first run a SELECT query to ensure that the correct rows are targeted. For example:

     SELECT * FROM employees WHERE employee_id = 5;
   

By following these steps, beginners can learn how to effectively use the DELETE operation in MySQL to remove specific records from tables such as employees, departments, and branches. This operation is essential for maintaining an accurate and up-to-date company database.

To gain complete access, login with gmail or outlook, no need of signup, click here