In earlier sections, we've covered how to insert data and retrieve data from a database table based on various conditions. In this tutorial, we'll focus on another critical task: updating existing records in a database table.
The UPDATE statement is used to modify existing data in a table.
UPDATEtable_nameSETcolumn1_name= value1,column2_name= value2,... WHEREcondition;
Here, column1_name, column2_name,... represent the names of columns or fields in a database table whose values you intend to update. You can also combine multiple conditions using the AND or OR operators, as explained in previous sections.
Warning: The WHERE clause in the UPDATE statement determines which record or records should be updated. If you omit the WHERE clause, all records in the table will be updated.
Let's explore some examples to see how this works in practice.
Imagine we have an employees table in our database with the following records:
+--------+--------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+--------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 | | 2 | Tony Montana | 2002-07-15 | 6500 | 5 | | 3 | Sarah Connor | 2005-10-18 | 8000 | 3 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 4 | | 5 | Martin Blank | 2008-06-24 | 5600 | NULL | +--------+--------------+------------+--------+---------+
This SQL statement updates the emp_name field in the employees table, setting a new value where the employee ID (emp_id) equals 3.
UPDATE employees SET emp_name = 'Sarah Ann Connor'
WHERE emp_id = 3;After executing the query, the resulting table will appear like this:
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 | | 2 | Tony Montana | 2002-07-15 | 6500 | 5 | | 3 | Sarah Ann Connor | 2005-10-18 | 8000 | 3 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 4 | | 5 | Martin Blank | 2008-06-24 | 5600 | NULL | +--------+------------------+------------+--------+---------+
Similarly, you can update multiple columns by specifying a comma-separated list of column name and value pairs. The following example updates the salary and dept_id fields for an existing employee in the employees table where the emp_id is 5.
UPDATE employees
SET salary = 6000, dept_id = 2
WHERE emp_id = 5;After executing the query, the resulting table will resemble this:
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 1 | | 2 | Tony Montana | 2002-07-15 | 6500 | 5 | | 3 | Sarah Ann Connor | 2005-10-18 | 8000 | 3 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 4 | | 5 | Martin Blank | 2008-06-24 | 6000 | 2 | +--------+------------------+------------+--------+---------+