Occasionally, a requirement to modify a table's column might arise by referencing the value of another column within the same table. This Answer will outline the process of updating a column in SQL based on the value of another column.
As an example, let's create a table named Users with the columns id, f_name, and l_name and insert some values in the table.
create table Users (id int,f_name varchar(255),l_name varchar(255));insert into Users values(0,'Ali','Hamza'),(1,'Umer','Ahmed'),(2,'Ibrahim','Khan');Select * from Users
Lines 1–3: We use the create table statement to create a Users table.
Lines 4–7: We insert values into the Users table using insert into statement.
Line 9: We print the Users table using Select * statement.
There are two choices for updating a column based on the value of another column:
Employing a WHERE clause.
Employing a CASE statement.
Where clauseBelow is the SQL query that updates the l_name column based on the value of the f_name column using a WHERE clause:
update Usersset l_name='Butt'where f_name='Ibrahim';Select * from Users;
Lines 1–3: We use the UPDATE statement that initially identifies the rows that satisfy the WHERE clause and then update the value of the l_name column.
Line 5: We print the updated Users table using Select * function.
Case clauseBelow is the SQL query that updates the l_name column based on the value of the f_name column using a Case clause.
update Usersset l_name = (CASEWHEN f_name = 'Ali'THEN 'Ahmed'WHEN f_name = 'Umer'THEN 'Hamza'WHEN f_name = 'Ibrahim'THEN 'Mughal'END);Select * from Users;
Line 1: We use the UPDATE statement that identifies the rows that satisfy each CASE clause and update the table.
Lines 2–9: The CASE clause checks if the f_name matches the given name. If it matches, then it updates it to the given name in the THEN clause.
Line 11: We print the updated Users table using the Select * statement.
WHERE vs. CASE clauseThe WHERE clause primarily serves the purpose of filtering rows based on specific conditions. It enables selective retrieval or updating of rows that satisfy particular criteria. The WHERE clause is efficient and straightforward for simple conditional filtering operations.
Conversely, the CASE clause is employed for conditional expressions, allowing the execution of different actions or assigning various values based on specified conditions. It provides enhanced flexibility in conditional logic and proves beneficial when updating a column with different values based on varying conditions.
We have learned two ways to update a column, but deciding between the usage of a CASE clause and a WHERE clause relies on the specific requirements and context of our query. Each clause serves distinct purposes and offers its own set of advantages.
Free Resources