SQL Query UPDATE

Introduction

Once you've made a table in a database, it's unlikely to change indefinitely. You will almost certainly need to change the data in it.

And, to assist you, there is a useful statement called UPDATE that you can use to modify the records as required.

What Is SQL Query UPDATE?

SQL provides the UPDATE command, which allows users to update existing data in tables. You can use this command to modify and alter some (or all) of the records in a table's single or multiple columns.

This is an important instruction because the data in any dataset changes, so it assists the user in keeping datasets up to date.

Let's take a look at the syntax of the UPDATE command.

Syntax for SQL Query UPDATE Command

Syntax

UPDATE table_name

SET column_1=value_1, column_2=value_2,...column_n = value_n

[WHERE condition];

1. The UPDATE statement informs the database system that you want to update the entries for the table named in the table name parameter.

2. The columns to be modified are enumerated after the SET statement and are equated to their newly updated values. These sections are separated by commas.

3. The WHERE clause condition specifies which records from the specified columns will be updated.

4. It is critical to recall that if the WHERE clause is not used, all records in the designated columns will be updated.

Example

First, we have to pick the table which we want to UPDATE

SELECT * FROM Customers;

Consider the Customers table having the following records −

Let’s update some records of the Customers table using the Update command in SQL

UPDATE Customers
SET age=24
WHERE customer_id='3';

Crack your next tech interview with confidence!

UPDATE in Single Column

By using the above Example we can UPDATE value in the single column

Update the column Customers and set the age to ‘24’ in all the rows where customer_id is 3.

UPDATE Customers
SET age=24
WHERE customer_id='3'                       

1. After writing the query, click on the execute button to check for errors

2. Let’s check the update by writing the following

UPDATE in Multiple Column

Sometimes we need to change record values from various columns in a database, and we can do so with the UPDATE command.

For example, if we need to update the Age and country details for a customer with customer_id equal to 4, we’ll use the following query in our table:

UPDATE Customers
SET age=27, country= 'USA'
WHERE customer_id='4'                       

1. After writing the query, click on the execute button to check for errors

2. Let’s check the update by writing the following

Important:- To separate the names and values of two columns when updating multiple columns, use a comma (,).

Updating All Records From a Column

When using UPDATE in SQL, we should always be cautious because without the WHERE clause, all column rows mentioned in the query are updated to the new value. If done inadvertently, this can have serious consequences. Consider an example of this sort of record updating.

If we want to update all the rows of the “country” columns, in our “Customers” table to the values “USA”, we’ll use the following query:

UPDATE Customers
SET country= 'USA'
                       

Output: The table customers will now look like, this

SQL Update Multiple Rows

Syntax

UPDATE TABLE_NAME
SET COLUMN_VALUE 
= CASE COLUMN_NAME
WHEN 'COLUMN_NAME1' THEN COLUMN_VALUE1
WHEN 'COLUMN_NAME2' THEN COLUMN_VALUE2
ELSE COLUMN_VALUE
END
WHERE TABLE_NAME IN('COLUMN_NAME1', 'COLUMN_NAME2');                     

Example : If if the item is ‘keyboard’, then its amount is set to 800 and if the item is ‘mouse’, then its amount is set to 500. Use the keyword UPDATE and WHEN to achieve this. This query behaves like an if-else if-else block.

Query

SELECT * FROM Orders;
UPDATE Orders
SET amount 
= CASE item
WHEN 'Keyboard' THEN 800
WHEN 'Mouse' THEN 500
ELSE amount
END
WHERE item IN('Keyboard', 'Mouse');                    

1. After writing the query, click on the Run SQL button to check for errors

2. Let’s check the update by writing the following

SELECT * FROM Customers;

3. The amount for Keyboard and Mouse have been updated to 800 and 500 respectively.

Conclusions

It is critical for database records to be kept up to date with ever-changing real-world data, and the UPDATE command in SQL allows users to do just that. You never have to be concerned about an out-of-date information. This command is a basic but powerful tool at our disposal.

Good luck and happy learning!