H2 Database - Update

The UPDATE query is used to update or modify the existing records in a table. We can use WHERE clause with UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

Following is the basic syntax of the UPDATE query.

UPDATE tableName [ [ AS ] newTableApas ] SET 
{ { columnName = { DEFAULT | expression } } [,...] } | 
{ ( columnName [,...] ) = ( select ) } 
[ WHERE expression ] [ ORDER BY order [,...] ] [ LIMIT expression ]

In this UPDATE syntax, we can combine more than one condition by using AND or OR clauses.

Example

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitap |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
|  7 | Muffy    |  24 | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+ 

If you want to get the customer table along with the given data, execute the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20), 
   salary number);  
INSERT into CUSTOMER values (1,  Ramesh , 32,  Ahmedabad , 2000); 
INSERT into CUSTOMER values (2,  Khilan , 25,  Delhi , 1500); 
INSERT into CUSTOMER values (3,  kaushik , 23,  Kota , 2000); 
INSERT into CUSTOMER values (4,  Chaitap , 25,  Mumbai , 6500); 
INSERT into CUSTOMER values (5,  Hardik , 27,  Bhopal , 8500); 
INSERT into CUSTOMER values (6,  Komal , 22,  MP , 4500); 
INSERT into CUSTOMER values (7,  Muffy , 24,  Indore , 10000);

The following command is an example, which would update ADDRESS for a customer whose ID is 6 −

UPDATE CUSTOMERS SET ADDRESS =  Pune  WHERE ID = 6;

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

The above query produces the following result.

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitap |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | Pune      |  4500.00 | 
|  7 | Muffy    |  24 | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+ 

To modify all ADDRESS and SALARY column values in CUSTOMERS table, we need not use the WHERE clause. The UPDATE query would be as follows −

UPDATE CUSTOMERS SET ADDRESS =  Pune , SALARY = 1000.00; 

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

The above query produces the following result −

+----+----------+-----+---------+---------+ 
| ID | NAME     | AGE | ADDRESS | SALARY  | 
+----+----------+-----+---------+---------+ 
|  1 | Ramesh   |  32 | Pune    | 1000.00 | 
|  2 | Khilan   |  25 | Pune    | 1000.00 | 
|  3 | kaushik  |  23 | Pune    | 1000.00 | 
|  4 | Chaitap |  25 | Pune    | 1000.00 | 
|  5 | Hardik   |  27 | Pune    | 1000.00 | 
|  6 | Komal    |  22 | Pune    | 1000.00 | 
|  7 | Muffy    |  24 | Pune    | 1000.00 | 
+----+----------+-----+---------+---------+