In this article, we will explore how to update and delete data using SQL, providing both theoretical insights and practical examples to help you become proficient in these essential database tasks. Updating allows you to modify existing records while deleting allows you to remove unwanted data from your database. You should always exercise caution when carrying out these two action.
Keywords:
sql update, update query in sql, sql update statement, sql command update, sql with as update, sql delete, sql delete row, delete a record in sql, drop row sql
SQL Update command and SQL Delete row table of contents
Learn SQL online
SQL Update and Delete syntax
Let’s first understand the basic concepts and syntax for updating and deleting data in SQL.
Updating Data (UPDATE statement):
To modify data in a database, you can use the UPDATE statement. The basic syntax is as follows:
UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
TABLE_NAME: The name of the table you want to update.
SET: Specifies the columns to be updated and their new values.
WHERE: Specifies the conditions that must be met for the update to occur. If you omit the WHERE clause, all records in the table will be updated.
We first use the Keyword UPDATE and specify which table we would like to update. Next, use the keyword SET. This time you specify the column that you want to alter and then specify the value you want to set it to by using the equals sign.
Finally, use the WHERE condition to specify which rows you want to update. It is often a good idea to write an equivalent SELECT statement first to check that your WHERE clause is correct and that it returns the expected set of rows that you intend to update.
If you want to update all of the rows in a table then you can omit the WHERE condition and all rows will be updated.
Deleting Data (DELETE statement):
To remove data from a database, you can use the DELETE statement. The basic syntax is as follows:
DELETE FROM table_name
WHERE condition;
TABLE_NAME: The name of the table from which you want to delete data.
WHERE: Specifies the conditions that must be met for the deletion to occur. If you omit the WHERE clause, all records in the table will be deleted.
In the above SQL Delete statement we first specify the keyword delete and then specify from which table you would like to delete data. The WERE clause is again optional. If you omit it then all rows in a table will be deleted.
SQL UPDATE and DELETE Examples
Let’s now have a look at some examples of updating and deleting data. To help visualise this let’s image we have a table called employees with the following structure and data:
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Smith | 50000 |
| 2 | Jane | Doe | 60000 |
| 3 | Mark | Johnson | 55000 |
Updating data
Form the data above we can see that John Smiths salary is 50,000. Suppose you want to increase John Smith’s salary to 55000. You can use the UPDATE statement in the following way:
UPDATE employees
SET salary = 55000
WHERE first_name = 'John' AND last_name = 'Smith';
This query will change John Smith’s salary to 55,000. Notice that the WHERE the condition will uniquely identify the second row with employee_id = 2 and so only this row will be updated with the new salary value of 55,000. you could also apply the WHERE condition such that employee_id = 2 to acheive the same effect in the WHERE clause. You could have multiple conditions by using the AND, OR and NOT logical operators and set up the WHERE condition to contain more than one row.
SQL delete data
Suppose you want to delete an employee with employee_id 3 from the table. You can use the DELETE statement:
DELETE FROM employees
WHERE employee_id = 3;
This query will remove the row corresponding to employee 3 from the employees table. It would although be advisable to first run the below select statement to check that you are in fact going to delete the intended record(s). Here we use the same WHERE condition as the DELETE statement and use the wildcard * in the select statement as a shorthand to retrieve all columns.
SELECT *
FROM employees
WHERE employee_id = 3;
Conclusion
Updating and deleting data in a database using SQL is a fundamental skill for managing and maintaining your data. By using the UPDATE and DELETE statements with the appropriate conditions, you can make targeted changes to your database while ensuring data accuracy and consistency.
Remember to exercise caution when performing data updates and deletions, especially in production databases, as they can have a significant impact on your data integrity. Always backup your data and test your SQL statements in a safe environment before applying them to your live database. With the knowledge and practice gained from this article, you can confidently manage your data with SQL.
Please see the other SQL learning articles below as part of this learning SQL online course.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
