SQL Update Statement or Update Query in SQL is used to modify the column data in tables. Earlier we looked into SQL Select and SQL Insert queries.
SQL Update
In a database the storage and retrieval of data is the most important aspect. But, there are cases when we have inserted some incorrect data by mistake or the data that is inserted needs some modification.
Modification of data that is existing in the database is a performance oriented task. In case if the number of records that needs to be updated is huge, the performance impact will be huge. SQL UPDATE statement is used for modification of existing records.
SQL Update Query Syntax
For Updating Selected Records:
|
1 2 3 4 |
UPDATE table_name SET column1 = value 1, column2 = value 2,.. column_n = value_n WHERE condition; |
In the syntax above the update happens based on the condition that is specified in the WHERE clause. Let’s try to understand the sql update query through some example.
Let’s say we have a Customer table with some data into it, below the SQL query to create table and populate with some data. I am using MySQL database, for other database vendors below queries will be slightly different.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE `Employee` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '', `age` int(2) DEFAULT NULL, `gender` varchar(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `Employee` (`id`, `name`, `age`, `gender`) VALUES (1, 'John', 30, 'M'), (2, 'Smith', 25, 'F'), (3, 'Henry', 20, 'M'), (4, 'Lisa', 32, 'M'); commit; |
Now notice that Lisa gender is wrongly inserted as ‘M’. So we can update it to ‘F’ using update query in sql statement like below.
|
1 2 3 |
UPDATE employee SET gender="F" WHERE id=4; |
For Updating All Records:
|
1 2 3 |
UPDATE table_name SET column1 = value 1, column2 = value 2,.. column_n = value_n |
In the syntax above the update happens without any condition and will update all the records of the table. Let’s say we want to remove Gender column values from Employee table. We can do the bulk update of all the rows using update query as shown below.
|
1 2 3 4 |
UPDATE employee SET gender=null; --sets gender value to NULL UPDATE employee SET gender=null; --clears gender value for all rows |
The most important point to consider with update query is the where clause, as the update is dependent on the WHERE clause.
The records that will get updated will be selected based on the WHERE clause and in case if there is no WHERE clause all the records of the table will get updated.
SQL Update Select
Sometimes we have a situation where we want to update one table data by selecting value from another table. Let’s suppose we have two tables – POSTS and AUTHORS. Below SQL query shows their structure and some sample data generated for our SQL Update Select query example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE `AUTHORS` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `POSTS` ( `id` int(11) NOT NULL, `title` varchar(20) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into authors (id, name) values (1, 'Pankaj'), (2, 'Anupam'), (3, 'Ram'); insert into posts (id, title, author_id) values (1, 'Java', 1), (2, 'Python', 2), (3, 'SQL', 3), (4, 'Android', 1); |
If you look at above setup, AUTHORS table seems useless because all it’s use is to map author name to the post. So we can add a column for author name in the POSTS table and populate it’s value from corresponding rows in AUTHORS table.
We can alter POSTS table using below query.
|
1 2 3 |
alter table POSTS add column author_name varchar(20); |
Below is the sql update with select query to update one table columns data from another table.
|
1 2 3 |
UPDATE POSTS SET author_name = (select name from AUTHORS where POSTS.author_id = AUTHORS.id); |
SQL Update Join Example
We can perform above update operation using joins too. Below SQL update with inner join query will have the same result as above sql update with select query.
|
1 2 3 4 5 |
UPDATE POSTS AS P INNER JOIN AUTHORS AS A ON P.author_id = A.id SET P.author_name = A.name; |
Note that all the above queries are for MySQL database, for other databases there might be small change needed. But the underlying operation and output will remain same.
That’s all for SQL update statement, I hope above update queries will help you in writing proper sql queries for update.
Reference: Oracle Documentation