Blog

Update SQL statement

After having seen SQL INSERT, let’s see now how to update records stored in database using SQL queries.

Database record updates are somewhat more delicate than insertions, since if we are not careful, we can end up overwriting all the records in a table.

As always in SrCodigoFuente I am going to show you real examples of use while we go through the different key points of the process.

I will end this tutorial with advanced ways to update records in case you dare to try or are looking for complete knowledge about how to use the UPDATE SQL statement.

Content

  • SQL command to update sql table
  • UPDATE SQL Examples
    • Example UPDATE SQL of a column
    • Example modify all the records of a table
    • Modify multiple columns of a table
    • Modify multiple columns of all rows in a table.
  • Advanced SQL UPDATE Examples
    • UPDATE SET one column equal to another
    • Increment a column of numeric type
    • Insert concatenation of the value of two columns in the value of another
  • SQL Update Tips

SQL command to update sql table

To update the various rows, also known as tuples, in a table, we will use the SQL UPDATE statement.

Sentence UPDATE allows to modify multiple records of a table, so be careful, with a single query you could modify ALL the records of the same table.

The syntax or structure of a SQL UPDATE query is the next:

UPDATE tabla
SET columna = valor;

This is the structure of a simple UPDATE query with no conditions, which would modify all the records in the indicated table.

The complete structure of this SQL statement is as follows:

UPDATE tabla
SET columna = valor
[WHERE condiciones];

Notice how I’ve written the WHERE statement in square brackets, I mean by this that this part is optional and not required.

UPDATE SQL Examples

You already know the structure of an SQL UPDATE query, now let’s see how we could use it in different cases.

I am going to show you below the most common and simple cases in which you might need use an UPDATE:

  1. update a column of one or more specific records.
  2. Update a column of all records in a table.
  3. Modify multiple columns of one or more rows of a table.
  4. Modify multiple columns of all rows in a table.

In the following examples I will work on a table store created with a CREATE query like the following:

CREATE TABLE tienda (
	id INT PRIMARY KEY,
	nombre VARCHAR(50) NOT NULL,
	direccion VARCHAR(150) NOT NULL,
	contacto VARCHAR(100),
	tlf VARCHAR(15)
);

Example UPDATE SQL of a column

To change the data of one or more specific records, we must use the WHERE clause. As we have said before, one or more conditions can be indicated, just like in a SELECT SQL query.

Next I leave you a UPDATE SQL example to update the data of certain records in the table store. To be exact I am going to modify the phone in the registry with id 33:

UPDATE tienda
SET tlf = ‘765 333 120’
WHERE id = 33;

Example modify all the records of a table

The next SQL UPDATE examplealthough practically said with the explanation of the UPDATE statement itself, I want it to make clear the operation of an UPDATE without WHERE:

UPDATE tienda
SET contacto = ‘srCodigoFuente’;

The example UPDATE would modify ALL the records of the table store overwriting your column data contact for the value srSourceCode.

Modify multiple columns of a table

Pay attention now to this query, since it does differ from what was seen in the previous ones in one key point: we are going to modify multiple columns of the same table.

Thanks to the fact that SQL is a simple language, you will see that the task in question is easy to solve. Basically we are going to separate the pairs column = value using commas.

Here the example:

UPDATE tienda
SET contacto = ‘srCodigoFuente’, tlf = ‘765 333 120’
WHERE direccion LIKE ‘%Madrid%’;

This time the query UPDATE updates the value of the columns contact and phone with the values srSourceCode and 765 333 120 respectively. Also, with the WHERE, I configure that the update is only done on the records that contain the city Madrid.

Modify multiple columns of all rows in a table.

I am going to end this series of examples with another variant: update the value of several columnss but for all records in the table store.

The following example changes the value of the column name and address for all records in the table store and leaves them empty:

UPDATE tienda
SET nombre = ‘’, direccion = ‘’;

Advanced SQL UPDATE Examples

In the previous section I have exposed you one by one the 4 most common uses of the UPDATE statement. Now I want to go a step further and expose you to other use cases that are also relatively common but somewhat more complex.

The use cases that I want to show you are several:

  1. Update the value of one column with the value of another.
  2. Increment the numeric value of a column of type INT.
  3. Insert the concatenation of the value of several columns in another column.

I am going to do the following examples by modifying the rows of a table created with the following CREATE TABLE statement:

CREATE TABLE producto (
	id INT,
	codigo VARCHAR(20),
	nombre VARCHAR(100) NOT NULL,
	descripcion_corta VARCHAR (255),
	descripcion TEXT,
	texto_busqueda VARCHAR(120),
	visitas INT,
	PRIMARY KEY (id)
);

UPDATE SET one column equal to another

These types of updates are useful for replicating data from one column to another in the same record. He UPDATE complete for this purpose it would look like this:

UPDATE producto
SET descripcion = descripcion_corta;

I have updated with this UPDATE SQL all the records of the product table so that the text of its column short description be equal to the wing of its column description.

Increment a column of numeric type

A fairly common UPDATE in large web developments is the action of increment the value of a column of numeric type of which, although we do not know its value, we know that we want to increase a certain value.

In the following example on the table product I will increase the column visits to 1 for a particular record with the id 98200:

UPDATE producto
SET visitas = visitas + 1
WHERE id = 98200;

Insert concatenation of the value of two columns in the value of another

To finish this series of more complex examples, I am going to show you how you can combine the UPDATE statement with SQL functions like CONCAT SQL.

If you have not seen the CONCAT function before, you should know that this function returns the concatenation of the values ​​passed as an input parameter:

CONCAT( texto1, texto2, valor3, valorN)

For the example I have chosen a practice that could be done in a real web environment: gather the value of the column name, and of the column code, to enter it in the column search_text, special column to perform searches directly in a single column. The SQL query is:

UPDATE producto
SET texto_busqueda = CONCAT ( nombre, '', codigo);

SQL Update Tips

For a complete understanding we will finish this SQL UPDATE query tutorial with a review of the concepts seen plus some important detail:

    • The UPDATE statement is used to update existing records in database tables.
    • An UPDATE query, at least, must contain the table to which it will take effect and the SET statement to modify the data in a column.
    • It can be accompanied by the WHERE clause, just like a SELECT or DELETE query, to configure the conditions that must be met by the rows that will be affected by the changes.
    • In the WHERE statement you can specify as many conditions as you want. As always, they must be joined by AND or OR.
    • If conditions are not indicated with a WHERE, the update will affect all the records in the table without exception.
    • The data entered through any SQL UPDATE will always have to comply with the constraints of the columns. For example, we will not be able to insert text in a column of type INT (integer).
    • In the SET statement, values ​​can be entered in as many columns as necessary. The pairs of column-value They must be separated by commas. Example: UPDATE product SET name = ”, search_text = ”;

Leave a Reply