H2 Database 简明教程

H2 Database - Update

UPDATE 查询用于更新或修改表中的现有记录。我们可以将 WHERE 子句和 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

以下是 UPDATE 查询的基本语法。

Following is the basic syntax of the UPDATE query.

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

在这个 UPDATE 语法中，我们可以通过使用 AND 或 OR 子句组合超过一个条件。

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

Example

考虑具有以下记录的 CUSTOMER 表。

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 | 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 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, '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);

以下命令是一个示例，它将更新 ID 为 6 的客户的 ADDRESS −

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;

现在，CUSTOMERS 表将具有以下记录。我们可以通过执行以下查询来检查客户表记录。

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 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

要修改 CUSTOMERS 表中的所有 ADDRESS 和 SALARY 列值，我们不需要使用 WHERE 子句。UPDATE 查询如下 −

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;

现在，CUSTOMERS 表将具有以下记录。我们可以通过执行以下查询来检查客户表记录。

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 | Chaitali |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+