Updating PostgreSQL data

The behavior of modifying data that is already stored in a database is called an update. You can update a single row, or you can update all the rows in the table, and you can update a subset of the rows. We can update each field independently, while the other fields are unaffected.

To update an existing row, use the update command. This requires three kinds of information:

1. The name of the table and the name of the field to update

2. New Value for field

3. which rows to update

SQL typically does not provide a unique identifier for a data row. So we can't directly declare which line we need to update. However, we can declare a condition that must be met by an updated row. Only when the primary key is present in the table (not depending on whether you declare it) can we reliably specify a separate row by selecting the condition that matches the primary key. The graphical database access tool relies on the above methods to allow us to update some rows independently.

For example, this command will price all products priced at 5 to 10:

UPDATE products SET Price = Ten WHERE price = 5;

Doing so may result in 0 rows, one or more rows of data being updated. If we try to execute an update that does not match any row, that's not a mistake.

Let's take a closer look at this command. The first is the keyword UPDATE followed by the table name. As usual, the table name can be decorated with a pattern, otherwise it will be found in the schema path. Then the keyword SET follows the field name with an equal sign and the new field value. The new field value can be any scalar expression, not just a constant. For example, if you want to increase the price of all products by 10%, you can use:

UPDATE Products SET Price = Price * 1.10;

As you can see, the expression of the new value may also refer to the existing value in the row. We also ignored the WHERE clause. If we ignore this clause, all the rows in the table are updated. If a WHERE clause appears, only the row that matches its condition is updated. Note that the equals sign in the SET clause is an assignment, and the equals sign in the where clause is a comparison, but this does not cause any ambiguity. Of course, the WHERE condition does not necessarily have to be an equality judge. You can also use a number of other operators. But the expression must derive a Boolean result.

You can also update more fields in an update command by listing more assignment statements in the SET clause. Like what:

UPDATE mytable SET A = 5, B = 3, c = 1 WHERE a > 0;

Updating PostgreSQL data