SQL

Course 16: Update Data: Update

140 Views
Oct 2, 2021
Course 16: Update Data: Update

5. Update data: update

 

 

5.1. General form of update

 

 

Modifying the value of a row in a table requires the use of the command update. Here is the syntax of this command.

Example 56. General form of update

UPDATE table_name
     SET column_name = new_value [, column_name = new_value, ...]
   [ WHERE predicate]

Again, the clause whereis very important. If it is omitted, then the modification will be done on all the rows of the table. An elementary precaution thus consists in starting by testing its clause whereby making one select, in order to check if the modifications which one wants to make will be done well on the good lines.

The modifications specified in the clauses setare made for all the lines at the same time . The operation is therefore the same as for the control . If an error is generated during the operation, all the modifications are canceled. delete

In particular, it is possible to write this stuff in SQL:

Example 57. A somewhat special UPDATE

update a_table   set a = b, b = a;


This command, which constitutes a classic trap of programming languages ​​(for beginners only), works in SQL: the values ​​of the columns aand bare well exchanged.

If it is not possible to update items belonging to more than one table at a time, the clause wherecan, on the other hand, query data from more than one table.

Finally, it should be noted that the integrity constraints must always be checked at the end of an order update. It is therefore possible that such a command will fail.

5.2. Update with a nested query

It is also possible to update the values ​​of a table with values ​​read from this same table or from another table. In other words, values ​​read by one select.

Take the following example: we have a table Marins, with, among other things, a column commune_naissance. This column bears the name of the municipality of birth of each sailor.

Our project is evolving, and we realize that it would be smarter to have a table Communes, and a join from the table Marinsto this table.

We therefore want to change the table Marinsto this new structure, and above all, automatically fill in the join column that we are going to create.

Example 58. Using updatewith a nested query

- creation of the table Marins 
 create   table Marins (
   id   int   primary   key ,
   name   varchar (30),
   common_birth   varchar (30)
);

 - creation of the Communes 
 table create the   Communes table (
   id   int   primary   key ,
   name   varchar (30),
);

 - update of the Marins 
 table alter   table Marins
  add   column id_commune   int ;

 - update of the column id_commune 
 update Marins
  set id_commune =
   ( select id   from Communes   where Communes.name =
    Marins.commune_naissance);


This example shows how the result of a type query selectcan be used to update a column. Everything happens as if, for each row of the table Marins, the nested query was executed, with the parameters of the current row. Of course, the result of this query must not be more than one line, otherwise an error will be generated.

Leave a Reply

Your email address will not be published. Required fields are marked *