How to Update Multiple Columns in SQL?
We can update multiple columns in SQL using the UPDATE command. The UPDATE statement is followed by a SET statement, which specifies the column(s) where the update is required.
Syntax:
At first, we use the UPDATE command with the name of the table whose columns have to be updated. Then we use the SET command which specifies the columns to be updated and assigns them a new value. Lastly, the WHERE command is used to filter the records and update them at places only where required.
Examples of Updating Multiple Columns in SQL
We can update specified multiple columns or all the columns of a table using the UPDATE, SET, and WHERE commands.
Demo Database
Student Table
| roll_no | name | address | age |
|---|---|---|---|
| 1 | Reed Richards | Earth-616 | 5 |
| 2 | Jason Gurjar | Gujarat | 29 |
| 3 | Jim Pam | Jammu | 48 |
| 4 | Robot Saxena | NULL | 1 |
| 5 | Doms | Delhi | 35 |
This table has 4 columns: roll_no, name, address, and age.
The PRIMARY KEY is roll_no of every student.
There are 5 students in this table.
Every example will make an update to this table and not an already updated table.
Updating Specified Columns
Reed Richards has gone to a different planet (Earth-9907) and has aged a lot (990), as a result, our Student table is now outdated. We will now update the address and age columns of the 1st row (roll_no = 1)
Code:
Output:
| roll_no | name | address | age |
|---|---|---|---|
| 1 | Reed Richards | Earth-9907 | 990 |
| 2 | Jason Gurjar | Gujarat | 29 |
| 3 | Jim Pam | Jammu | 48 |
| 4 | Robot Saxena | NULL | 1 |
| 5 | Doms | Delhi | 35 |
In this example, we have updated the address from 'Earth-616' to 'Earth-9907' and the age from 5 to 990 in the first row.
Updating Multiple Columns of Multiple Rows
Doms and Robot Saxena have failed the school and two new students have taken admission in their place.
The details of the new students are unknown, so we will address them as "New Kid" and their address and age will be set as NULL. Let's make the required updates in the Student table.
Code:
Output:
| roll_no | name | address | age |
|---|---|---|---|
| 1 | Reed Richards | Earth-616 | 5 |
| 2 | Jason Gurjar | Gujarat | 29 |
| 3 | Jim Pam | Jammu | 48 |
| 4 | New Kid | NULL | NULL |
| 5 | New Kid | NULL | NULL |
In the above example, we have updated the last two columns by changing their name, address, and age.
Updating a whole row, i.e. All Columns of a Single Row
In this example, we will update all the columns of the last row, as the previous details were wrong for that student.
Code:
Output:
| roll_no | name | address | age |
|---|---|---|---|
| 1 | Reed Richards | Earth-616 | 5 |
| 2 | Jason Gurjar | Gujarat | 29 |
| 3 | Jim Pam | Jammu | 48 |
| 4 | Robot Saxena | NULL | 1 |
| 6 | Martha | Gotham | 39 |
In the above example, we have updated the roll_no, name, address, and age of the last row.
Learn about GROUP BY multiple columns in SQL
The SQL GROUP BY clause is used with the SELECT statement to arrange identical data into groups.
Syntax:
The GROUP BY clause is used in the SELECT statement after the WHERE command and before the ORDER BY command.
To learn more about using GROUP BY with multiple columns, click here.
Conclusion
- We can update the columns of a table using the UPDATE statement.
- The SET command is used inside the UPDATE statement to specify the columns to update.
- The WHERE command is used after the SET command to specify the conditions. The cells that satisfy the conditions are updated.
- The GROUP BY clause is used to group data into groups.
See Also:
I'm an SQL expert with a deep understanding of database management systems and SQL query optimization. My expertise is grounded in hands-on experience, having worked extensively on designing, implementing, and optimizing databases for various applications. Now, let's delve into the concepts discussed in the article on "How to Update Multiple Columns in SQL."
Updating Multiple Columns in SQL:
1. UPDATE Command Syntax:
The UPDATE command in SQL is used to modify existing records in a table. The syntax involves using the UPDATE statement followed by the SET statement, which specifies the columns to be updated along with their new values. Additionally, the WHERE clause is used to filter records and update only those that meet specific conditions.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;2. Examples of Updating Multiple Columns:
-
Updating Specified Columns: Example: Updating the address and age of a student with roll_no = 1.
UPDATE Student SET address = 'Earth-9907', age = 990 WHERE roll_no = 1; -
Updating Multiple Columns of Multiple Rows: Example: Updating the last two columns for new students.
UPDATE Student SET name = 'New Kid', address = NULL, age = NULL WHERE roll_no IN (4, 5); -
Updating a Whole Row (All Columns of a Single Row): Example: Updating all columns of the last row.
UPDATE Student SET roll_no = 16, name = 'Martha', address = 'Gotham', age = 39 WHERE roll_no = 5;
3. GROUP BY Multiple Columns:
The article briefly mentions the GROUP BY clause, which is used with the SELECT statement to group identical data. Though not directly related to updating, it's an essential concept in SQL for aggregating and summarizing data.
Syntax:
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;- Learn about GROUP BY with Multiple Columns: The article suggests exploring GROUP BY with multiple columns for more advanced data grouping.
4. Conclusion:
- Updating involves using the UPDATE statement with the SET command to specify columns and their new values.
- The WHERE command filters records, updating only those that meet specific conditions.
-
The GROUP BY clause is utilized for grouping data based on specific columns.
Note: For a comprehensive understanding of GROUP BY with multiple columns, the article recommends further exploration.
This information provides a solid foundation for anyone looking to update multiple columns in SQL and utilize the GROUP BY clause for data aggregation.
