SQL UPDATE Statement

SQL UPDATE Statement

TheUPDATEstatement 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 theWHEREclause.
• 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 SQLUPDATEstatement:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Example: Update a Single Column

Assume we have created a table namedCUSTOMERSusing 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:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003Kaushik23Kota2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.006Komal22Hyderabad4500.007Muffy24Indore10000.00

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:

IDNAMEAGEADDRESSSALARY3Kaushik23Kota2500.00

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 theAGEandADDRESSof 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:

IDNAMEAGEADDRESSSALARY2Khilan26Chennai1500.00

Update All Rows in SQL

If you omit theWHEREclause in anUPDATEstatement, the changes will be applied toevery rowin 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:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2200.002Khilan25Delhi1650.003Kaushik23Kota2200.004Chaitali25Mumbai7150.005Hardik27Bhopal9350.006Komal22Hyderabad4950.007Muffy24Indore11000.00

SQL UPDATE with Subquery

TheUPDATEstatement in SQL can also use asubqueryto 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 namedSALARY_RAISEScontaining updated salary information as shown below:

CREATE TABLE SALARY_RAISES (

  ID INT,

  NEW_SALARY DECIMAL(18, 2)

);

Next, insert the new salary values into theSALARY_RAISEStable:

INSERT INTO SALARY_RAISES VALUES

(1, 2200.00),

(3, 2700.00);

Now update theSALARYcolumn in theCUSTOMERStable:

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:

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2200.003Kaushik23Kota2700.00

Important Points About UPDATE Statement

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

• Always use aWHEREclause 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.