Update database records

It is certainly important to understand that this article, Update database records, will deal with content about SQL language – UPDATE, more specifically how to change records within a database table.

First, when it comes to changing records in a Database (DB), it is important to review two concepts: creating tables and inserting records.

Thus, it is recommended to read the article Insert database records, also to make it easier, the same table and records inserted for your practical experiment will be used.

Syntax UPDATE

Finally, you can now move on to the command syntax UPDATE. Just to illustrate, as shown below, the UPDATE has as essential components of its syntax, the table_name; the name of, or the name of, the columns and the respective values for each defined column; in addition, the possibility to apply filter criteria(s).

UPDATE nome_da_tabela 
SET nome_coluna = valor 
    [,nome_coluna = valor...]
[WHERE]
   [critério(s) de filtro(s)];

On the other hand, it is important to point out some aspects about the application of this command:

• An UPDATE command applies the change of record(s) in a single table.
• An UPDATE command can change none, one, or multiple rows of records.
• At least one UPDATE command must change the value of a column.
• You can apply changing values to more than one column in a single UPDATE command.
• An UPDATE command can be applied without the WHERE clause, but this way it will affect all rows of records in the table (for example, if the table has a thousand records, the thousand will be changed to the same value).
• The WHERE clause filters the rows of records that will be applied in an UPDATE command.

Practical Experiment

To generate the practical experiment, the PESSOA table and its records inserted in the article Insert ORACLE database records will be used. However, to make it easier, below is an illustration of the table with its records.

In the same way, this practical experiment can be applied through a virtual machine, or a normal machine that has an ORACLE version installed (it can be the Express Edition version).

Or, you can use Oracle Live SQL, an online version via browser for testing provided by Oracle itself (you just need to have a valid account on their website). This article will use the Oracle Live SQL online tool to carry out practical experiments.

Applying UPDATE Commands

UPDATE PESSOA 
   SET NOME = 'MARIA APARECIDA'
 WHERE ID = 1;
-- 1 row(s) updated.
UPDATE PESSOA 
   SET GENERO = 'X'
 WHERE ID = 3;
-- ORA-02290: check constraint...
UPDATE PESSOA 
   SET DATA_NASCIMENTO = '07/11/1974'
WHERE ID = 3;
-- ORA-01843: not a valid month...
UPDATE PESSOA 
   SET DATA_NASCIMENTO = TO_DATE('07/11/1974', 'DD/MM/YYYY')
 WHERE ID = 3;
-- 1 row(s) updated.
UPDATE PESSOA 
   SET NOME = 'CLAUDIA MARIA', GENERO = 'F'
 WHERE ID = 3;
-- 1 row(s) updated.

Namely, all values related to text fields, such as the NOME (NAME) and GENERO columns must be enclosed in apostrophes. On the other hand, to accurately pass the date to ORACLE, it is important to use the TO_DATE function. Function that converts text enclosed in apostrophes into a date in the following format, as in the DATA_NASCIMENTO (DATE_BIRTH) field.

Explanation of SCRIPT above

Line 1’s UPDATE changed the NOME field of the record in the ID = 1:

• With the application of this UPDATE command, the name of the record with ID equal to 1 was changed, in this case the NOME field changed from MARIA to MARIA APARECIDA.

Line 5 UPDATE does not change the record:

• In the same way as in an INSERT, all UPDATEs commands that are applied go through the checks of the constraints of the tables, in this case the GENERO field only accepts the M or F values, when X is passed, a constraint violation occurs.

The UPDATE of line 9 changed record:

• Now the data value has been involved by the TO_DATE conversion function, removing any possibility of inversion or understanding, as the format is passed for checking.

Line 13 UPDATE does not change the record:

• The data field to be interpreted correctly within an ORACLE database must use the TO_DATE function to pass a value. Thus, there is no risk of inverting month with day, or day with month due to the regionalization used in the installation, or of not applying the change, as in this example.

UPDATE on line 17 does not change the record:

• In this example, two fields have their values changed. The name field is passed to CLAUDIA MARIA and the GENERO is passed to F, demonstrating an example with more than one field.

Features

Reinforcing the features of the command:

• Change only records from a single table.
• It may or may not inform all columns.
• In transactional databases, UPDATEs are only effective using COMMIT, if you don’t want them to be effective, you can cancel with ROLLBACK.
• Date field, or date time, use the TO_DATE function to inform value (this prevents date inversion due to American or Latin format).
• In the SET clause the fields do not need to be in the order of creation of the table, this order can be random. Remember that the fields must be separated by commas.

Good habits

• As good practices, an UPDATE should not be applied without the WHERE clause:
  • As it can affect all record lines with the same value.
  • As an example, if you take the UPDATE from line 1 and remove the WHERE to apply it, all records will have a value for the column name MARIA APARECIDA.
  • After applying a COMMIT to effect this UPDATE, it would not be possible to return the previous values.
  • This recovery being very complicated (in some cases only being possible with the BACKUP of the database; and even then with loss of some values).
  • There are risks even with BACKUP of not being able to return all values.
• Another good practice is to apply a SELECT with the same WHERE clause that the UPDATE will be applied to, to check the number of records that can be affected by the command.
  • This check before applying the UPDATE can indicate if something is not expected.
  • Example: it was to change a row, but the check SELECT is showing 3 rows that can be affected with this WHERE.
  • This indicates that the WHERE should be rechecked, there may be an error, a missing filter, etc.
  • Remembering, the SELECT command does not change the records, it is a record view feature.

Conclusion

Finally, changing records in a database is the subsequent step in creating and inserting records in a table. Therefore, the focus of this article was to present in a simple and practical way the subject of changing records in an ORACLE database table.

However, the use of a methodology based on practical experiments aimed at a problem helps in understanding and facilitates further deepening of the topic. To see more articles related to Database, click here.