The UPDATE Statement
When we need to modify existing row, we need to use
the UPDATE statement. If needed, we can update multiple records at the
same time. For the UPDATE to be successful the user must have data manipulation
privileges (UPDATE privilege) on the table or column and the updated value must
not conflict with all the applicable constraints (such as primary keys, unique
indexes, CHECK constraints, and NOT NULL constraints).
Syntax
UPDATE table_name
SET column_name = value, column_name = value...
WHERE condition;
|
table_name
|
It is
the table name
|
|
SET
|
Modification
clause
|
|
column_name
|
Existing
column name which is to be modified.
|
|
value
|
New
value to be updated to the column or subquery for the column.
|
|
condition
|
Basis
on which the modification is performed. It can include column names,
expressions, constants, subqueries, and comparison operators
|
Example
Let us
see the output row from students table where the rollno is 4.
select * from students
where rollno=4;
We will update the lastname for the student whose roll no is 4 with use of update statement example as shown below.
update students
set lastname='Green'
where rollno=4;
commit;
select * from students
where rollno=4;
We can see that the Oliver’s lastname has been updated from Queen to Green.
We must prefer the primary key to identify a single row. Using
other columns may unexpectedly cause several rows to be updated. If we omit the
WHERE clause, ALL records will be updated.
update students
set lastname='Green';
commit;
The above
query will update all the rows in students table and set lastname to Green.
select * from students;
So the UPDATE statement should be used with caution.
Updating Multiple Columns with a Subquery
It is
possible to update multiple columns in the single query. Also there is
provision to take the value which has to be set as an output from the sub
query. A subquery is a query that is nested inside a SELECT,
INSERT, UPDATE, or DELETE statement, or inside another subquery.
A subquery can be used anywhere an expression is allowed. A subquery
is also called an inner query or inner select, while the statement containing a
subquery is also called an outer query or outer select. The SELECT query of a
subquery is always enclosed in parentheses.
Syntax
UPDATE table_name
SET column_name = (SELECT column_name FROM table_name WHERE
condition),
column_name = (SELECT column_name FROM
table_name WHERE condition)
WHERE condition;
Example
Let us
update students table using subquery on the same students table as shown below.
Here we are updating firstname and city column for row with roll no 45.
update students
set lastname=(select firstname from students where rollno=14),
CITY=(select CITY
from students where rollno=21)
where rollno=45;
commit;
The
lastname column in the outer query is populated by subquery which refers to
firstname column of students where roll no is 14.
The city
column the outer query is populated by subquery which refers to city column of
students where roll no is 21.
Before
update the output of students table is as shown below.
select * from students;
After
update the output of students table is as shown below.
select * from students;
Updating Rows Based on another Table
We can
update multiple columns as well as from multiple sub queries and also refer to
multiple tables.
Syntax
UPDATE table_name
SET column_name = (SELECT column_name FROM table_name_1 WHERE
condition),
column_name
= (SELECT column_name FROM table_name_2 WHERE condition)
WHERE condition;
Example
Now we
will update students_copy table by taking values from students table.
select * from students_copy;
Below is
an update statement where the lastname for Barry is Allen is updated to Kara. The
lastname column in the outer query is populated by subquery which refers to
firstname column of students where roll no is 14.
update students_copy
set lastname=(select firstname from students where rollno=14)
where rollno=45;
commit;
Let us check the output after
update.
select * from students_copy;
