Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

update
modify
sql
database

How to use the update statement in SQL

Educative Answers Team

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:

  1. With where statements
  2. Without where statements

Syntax

Let’s look at both implementations of the update statement.

1. Using WHERE clause

In 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_name
SET columnA = valueA, columnB = valueB, ....
WHERE condition_statement

2. Without WHERE clause

The 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_name
SET columnA = valueA, columnB = valueB, ....

Examples

Now that we have seen how to use UPDATE in SQL, let’s look at a few examples to improve our understanding.

1. Updating with a single condition

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 employee
SET e_name = "Danny"
WHERE e_name = "Jane";

/*Printing the table to show changes*/

SELECT *
FROM employee;

2. Updating 2 columns with one condition

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 employee
SET e_name = "Jane", salary = 7000
WHERE e_name = "Danny";

/*Printing the table*/

SELECT *
FROM employee;

3. Changing a single column using multiple conditions

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 employee
SET salary = 500
WHERE age < 40 OR e_name = "Jane";

/*Printing the table*/

SELECT *
FROM employee;

4. Using UPDATE without WHERE

Not 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 employee
SET salary = 50;

/*Printing the table*/

SELECT *
FROM employee;

RELATED TAGS

update
modify
sql
database
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring