SQL Update Statement - Update Query in SQL With Examples

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:

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.

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.

For Updating All Records:

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.

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.

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.

Below is the sql update with select query to update one table columns data from another table.

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.

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

By admin

Leave a Reply