Using UPDATE SQL command
Let's take an example of a real-world problem. These days, Facebook provides an option for Editing your status update, how do you think it works? Yes, using the Update SQL command.
Let's learn about the syntax and usage of the UPDATE command.
UPDATE command
Data Manipulation Language (DML) provides SQL Commands that are used for managing data in the database. DML commands are not auto-committed. It means changes made by DML Commands are not permanent to the database, it can be rolled back.
Data Manipulation Language supports three main commands. These are: INSERT, UPDATE and DELETE commands. In this article, we will discuss only the UPDATE statement in SQL.
SQL UPDATE Statement
SQL UPDATE command, as the name suggests, is used for updating or modifying one or more records of a table in a database. For example, you must have noticed that Facebook provides an option for editing your status update, how do you think it works? Yes, by using the UPDATE SQL command.
Syntax:
UPDATE table_name
SET column_name = new_value
WHERE some_condition;
Let us consider a table 'student' with the following three fields and three records.
Using the UPDATE statement alone can modify the whole record or it can make changes in the whole table. In order to modify or update a particular record, use the WHERE clause with the UPDATE statement. It will only update the record that satisfies the WHERE condition. We will look into the WHERE clause in detail in another article.
Now, let us discuss different use cases of the UPDATE command in SQL using different examples.
Example: Use UPDATE without WHERE clause
The below SQL query does not use the WHERE clause. So, it updates the whole column age with 18 value.
UPDATE student
SET age = 18;
Output:
Example: Use UPDATE with WHERE clause
The below SQL query uses the WHERE clause. So, it only updates the age column for the student with id number 102.
UPDATE student
SET age = 18
WHERE id = 102;
Output:
Example: Update Multiple Columns
The below SQL query updates values of multiple columns using a single UPDATE statement. This example will update two columns of the record which has id equals 103.
UPDATE student
SET name = 'Alan', age = 17
WHERE id = 102;
Output:
Example: Increment Integer Value
When we have to update any integer value in a table, then we can fetch and update the value in the table in a single statement. In this example, we will update the age column of the student table every year for every student using the below SQL query. As you can see, we have used age = age + 1 to increment the value of age by 1. Note that this style only works with integer value. Compare the result of this query with the original table, you will observe an increment of 1 in the age column for all records.
UPDATE student
SET age = age+1;
Output:
Example: Use UPDATE with SELECT statement
This method allows for updating a table by selecting records from another table.
Syntax:
UPDATE table1
SET column1 = (SELECT expression1 FROM table2 WHERE conditions)
WHERE conditions;
The below SQL statements shows subquery which means a query inside a query. When dealing with a query inside a query, the subquery is executed first and then the outer query executes using the output of the inner query. In this case, the first inside query returns all the supplier_names depending on the condition and then the outer query uses supplier_names to update the Customers table. It sets all the names to the name returned by the inner query.
UPDATE customers
SET name = (SELECT supplier_name
FROM suppliers
WHERE suppliers.supplier_name = customers.name
)
WHERE age < 25;
Example: Use UPDATE with JOIN
SQL UPDATE JOIN updates one table using another table and join condition. The below syntax is used to update the table after performing the JOIN operation on two tables. The easiest and common way is to use the join clause in the update statement and use multiple tables in the update statement.
UPDATE customer_table
INNER JOIN Customer_table
ON customer_table.rel_cust_name = customer_table.cust_id
SET customer_table.rel_cust_name = customer_table.cust_name
Note: Read this article for JOINS: SQL JOINS
Example: Use UPDATE with DATE
This example will show you how to update a date & time field in SQL.
Syntax:
UPDATE table_name
SET Column_Name = 'YYYY-MM-DD HH:MM:SS'
WHERE Id = value;
The below query takes a table in which we want to update the date and time fields. It changes the END DATE field of the first row which id is 1.
UPDATE table1
SET EndDate = '2014-03-16 00:00:00.000'
WHERE Id = 1;
Note: You should always remember that SQL must attach default 00:00:00.000 automatically.
Conclusion
In this article, we discussed UPDATE command of SQL to update multiple records in a table. We discussed how it is used with WHERE clause, JOINS, DATE etc. with the help of different examples.
