SQL UPDATE Statement

---

---

SQL UPDATE Statement

The UPDATE statement in SQL is used to modify existing records in a table. You can update one or more columns for one or more rows based on a condition (using the WHERE clause).

It is useful when you need to change values in your database, such as correcting data, adjusting prices, updating statuses, and more.

• You can update all rows or only specific rows using the WHERE clause.
• If you omit the WHERE clause, all rows in the table will be updated.
• You can update one or more columns in a single statement.

Syntax

Following is the basic syntax of the SQL UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Update a Single Column

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert few records into this table using the INSERT statement as follows:

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows:

Now, we use the following query to update the salary of the customer with ID = 6:

UPDATE CUSTOMERS
SET SALARY = 2500.00
WHERE ID = 6;

Following is the output obtained:

uery OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Run the following query to verify the update:

SELECT * FROM CUSTOMERS WHERE ID = 3;

You get the following result:

SQL Update Multiple Columns

You can also update multiple columns in a single UPDATE statement in SQL. To do so, you just need to separate the column-value pairs with commas.

Syntax

Following is the syntax to update multiple rows and columns in SQL:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE condition;

Example

In the following example, we update both the AGE and ADDRESS of the customer with ID = 2:

UPDATE CUSTOMERS
SET AGE = 26, ADDRESS = 'Chennai'
WHERE ID = 2;

Following is the output obtained:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Verify the updated data using the following query:

SELECT * FROM CUSTOMERS WHERE ID = 2;

The table will now show:

Update All Rows in SQL

If you omit the WHERE clause in an UPDATE statement, the changes will be applied to every row in the table.

This can be useful when you need to modify a column's value across all records, for example, adjusting prices or resetting statuses. However, use this approach carefully, as it will overwrite data in all rows without exception.

Syntax

Following is the basic syntax to update all rows in SQL:

UPDATE table_name
SET column1 = value1, column2 = value2, ...;

Example

The following statement increases the salary of each customer by 10%:

UPDATE CUSTOMERS
SET SALARY = SALARY * 1.10;

Following is the output obtained:

uery OK, 7 rows affected (0.02 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

Verify the updated data using the following query:

SELECT * FROM CUSTOMERS;

The updated table is as follows:

SQL UPDATE with Subquery

The UPDATE statement in SQL can also use a subquery to update values in one table based on data from another table. A subquery is a nested query that provides a value or set of values for the main update operation.

This approach is useful when the new values you want to assign depend on matching data in a different table. For example, you might want to update a customer's total amount based on their latest order in another table.

Syntax

Following is the basic syntax to use a subquery with the SQL UPDATE statement:

UPDATE table1
SET column1 = (SELECT column2 FROM table2 WHERE condition)
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);

Example

Let us create another table named SALARY_RAISES containing updated salary information as shown below:

CREATE TABLE SALARY_RAISES (
  ID INT,
  NEW_SALARY DECIMAL(18, 2)
);

Next, insert the new salary values into the SALARY_RAISES table:

INSERT INTO SALARY_RAISES VALUES
(1, 2200.00),
(3, 2700.00);

Now update the SALARY column in the CUSTOMERS table:

UPDATE CUSTOMERS AS C
SET SALARY = (
  SELECT NEW_SALARY FROM SALARY_RAISES AS S
  WHERE S.ID = C.ID
)
WHERE EXISTS (
  SELECT 1 FROM SALARY_RAISES AS S
  WHERE S.ID = C.ID
);

Following is the output obtained:

Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

You can check the updated records using the following query:

SELECT * FROM CUSTOMERS WHERE ID IN (1, 3);

You will get the following result:

Important Points About UPDATE Statement

Following are the important points to remember when using the UPDATE statement in SQL:

• Always use a WHERE clause to avoid updating all records unintentionally.
• You can update one or multiple columns at once.
• Use subqueries for dynamic or conditional updates based on other tables.
• The UPDATE statement is supported by most relational databases like MySQL, PostgreSQL, Oracle, and SQL Server.