H2 Database - Update

Donald Green Donald Green
Feb 3, 2022 - 01:53
0 22
H2 Database - Update

An UPDATE query is used to update or modify existing records in a table. We can use a WHERE clause with an UPDATE query to update the selected rows, otherwise it will affect all rows.

Syntax

Following is the basic syntax of an UPDATE query.

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

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

example

Consider a CUSTOMER table that has 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 | Chaitali | 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 data, run the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20),
   salarynumber);  
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, 'Chaitali', 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 that would update ADDRESS for a client whose id is 6 −

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

The CUSTOMERS table will now have the following entries. We can check the customer table entries by running the following query.

SELECT * FROM CUSTOMERS;

The above query gives 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 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | Komal | 22 | Pune | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

To change all the values ​​of the ADDRESS and SALARY columns in the CUSTOMERS table, we do not need to use the WHERE clause. The UPDATE request will be as follows:

UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00;

The CUSTOMERS table will now have the following entries. We can check the customer table entries by running the following query.

SELECT * FROM CUSTOMERS;

The above query produces the following result −