Given a database table, we can modify elements of the records. This modification, in relation to the update statement, is when an already existing field is changed to be something else.
Within this, there are two implementations of the update statement:
where statementswhere statementsLet’s look at both implementations of the update statement.
WHERE clauseIn this implementation, we specify a condition_statement on the basis of which the modification will take place. This ensures that one or multiple columns can be modified given they fulfill the condition specified.
The first implementation uses the WHERE clause hence the syntax is as follows:
UPDATE table_nameSET columnA = valueA, columnB = valueB, ....WHERE condition_statement
WHERE clauseThe second implementation does not use the WHERE statement, hence can be tricky to work with. This applies the UPDATE method on all values specified in the SET statement. The following syntax is followed:
UPDATE table_nameSET columnA = valueA, columnB = valueB, ....
Now that we have seen how to use UPDATE in SQL, let’s look at a few examples to improve our understanding.
In the code below, the code changes the value of e_name from Jane to Danny. Hence, only one record will be affected.
CREATE TABLE employee(e_name varchar(20),age int,salary int);INSERT INTO employee(e_name,age,salary)VALUES("John", 10, 0);INSERT INTO employee(e_name,age,salary)VALUES("Jane", 50, 1000);INSERT INTO employee(e_name,age,salary)VALUES("Johnny", 20, 250);INSERT INTO employee(e_name,age,salary)VALUES("Jacob", 40, 5000);/*Printing the table*/SELECT *FROM employee;/*Updating the value of Jane to Daanny*/UPDATE employeeSET e_name = "Danny"WHERE e_name = "Jane";/*Printing the table to show changes*/SELECT *FROM employee;
The code below changes the values in the record where e_name is Danny. It changes the value of this record such that e_name is set to Jane and salary is set to 7000. In this case, only one record is changed.
CREATE TABLE employee(e_name varchar(20),age int,salary int);INSERT INTO employee(e_name,age,salary)VALUES("John", 10, 0);INSERT INTO employee(e_name,age,salary)VALUES("Danny", 50, 1000);INSERT INTO employee(e_name,age,salary)VALUES("Johnny", 20, 250);INSERT INTO employee(e_name,age,salary)VALUES("Jacob", 40, 5000);/*Printing the table*/SELECT *FROM employee;/*Updating name to Jane and salary to 700 where name is Danny*/UPDATE employeeSET e_name = "Jane", salary = 7000WHERE e_name = "Danny";/*Printing the table*/SELECT *FROM employee;
The code below changes the salary of all records that either have age less than 40 or have e_name as Jane, to 500. This affects 3 records in the table created below.
CREATE TABLE employee(e_name varchar(20),age int,salary int);INSERT INTO employee(e_name,age,salary)VALUES("John", 10, 0);INSERT INTO employee(e_name,age,salary)VALUES("Jane", 50, 1000);INSERT INTO employee(e_name,age,salary)VALUES("Johnny", 20, 250);INSERT INTO employee(e_name,age,salary)VALUES("Jacob", 40, 5000);/*Printing the table*/SELECT *FROM employee;/*Updating salary to 500 where either age is less than 40 or name is Jane*/UPDATE employeeSET salary = 500WHERE age < 40 OR e_name = "Jane";/*Printing the table*/SELECT *FROM employee;
UPDATE without WHERENot using the WHERE clause simply sets the salary of all records to 50.
CREATE TABLE employee(e_name varchar(20),age int,salary int);INSERT INTO employee(e_name,age,salary)VALUES("John", 10, 0);INSERT INTO employee(e_name,age,salary)VALUES("Jane", 50, 1000);INSERT INTO employee(e_name,age,salary)VALUES("Johnny", 20, 250);INSERT INTO employee(e_name,age,salary)VALUES("Jacob", 40, 5000);/*Printing the table*/SELECT *FROM employee;/*Updating without WHERE condition*/UPDATE employeeSET salary = 50;/*Printing the table*/SELECT *FROM employee;
Free Resources