UPDATE-SET in SQL (Updating column values for some specific rows)
In SQL, in an already existing table what if you want to change the values in the rows satisfying a particular condition?
SQL's UPDATE-SET commands are used to serve the above purpose.
SET column_name = new_value
WHERE (condition on the rows);
For this tutorial we shall make use of employee_performance:
CREATE TABLE employee_performance( employee_id int, department varchar(20), education_level varchar(20) , gender char(1), no_of_trainings int, age int, previous_year_rating numeric(2,1), length_of_service int, KPIs_met char(1), avg_training_score numeric(5,2), promoted_or_not varchar(3)); INSERT INTO employee_performance(employee_id,department,education_level,gender,no_of_trainings,age,previous_year_rating,length_of_service,KPIs_met,avg_training_score,promoted_or_not) VALUES (1001,'Marketing','Graduate','M',2,24,NULL,1,'Y',69.5,'N'), (1002,'Analytics','Post-Graduate','M',5,32,4.5,7,'Y',89.5,'Y'), (1003,'R&D','Graduate','F',5,44,5,10,'Y',87,'Y'), (1004,'HR','Graduate','M',1,32,3,3,'Y',54,'N'), (1005,'Marketing','PhD','M',0,38,4,7,'N',79.5,'N'), (1006,'IT','Graduate','F',2,23,NULL,0,'Y',83.5,'N'), (1007,'Analytics','Post-Graduate','M',2,28,5,4,'Y',78.5,'Y');
Our data looks as follows:
SELECT * FROM employee_performance
Task: Change no_of_trainings to 10 for analytics department.
UPDATE employee_performance SET no_of_trainings = 10 WHERE department = 'Analytics'; SELECT * FROM employee_performance;
Note: If you skip the WHERE condition then all of the rows in the data will be updated like shown below:
UPDATE employee_performance SET no_of_trainings = 10 SELECT * FROM employee_performance;