SQL – UPDATE Statement part-1

update

admin DML Statements, Oracle , ,

Hi Geeks,
We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement in SQL.

UPDATE statement:
UPDATE statement is used to alter/modify any inserted record in the table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

table_name:
This is table name which should be updated. Table name should be mentioned right after the UDPATE keyword.

set:
SET keyword helps Oracle Engineto identify which column should be updated.

column:
This is the column of the table where we need to set new value. We can update more than one column in a single go. Just specify the columns as showing in the syntax. Examples will clease all your confusion.

value:
As showing in the syntax, it is a value which needs to be set in that column. We can specify a STRING or an EXPRESSION or DEFAULT keyword.

  • STRING – If we add value as a string, it should be enclosed within single quotes (‘ ‘).
  • DEFAULT – When we specify DEFAULT keyword, Oracle checks if there are any DEFAULT value specified in the definition of the column and update rows with that DEFAULT value. If Oracle does not find any DEFAULT value for that column then it updates rows with null.
  • EXPRESSION – It can be a column from the same table or a column from another table in case of correlated query. Also, we can use different types of Functions to get desired output and set to the respective column.

where:
Where clause helps to limit the rows or let user decide which particular rows shoud be updated. If we ignore this clause, Oracle will update all the rows in the table. So, be very careful while using this statement.

As we have covered all important aspects of UPDATE statement, Let’s do some examples. I have created a table test_upd and inserted some rows. We will use this table to see how UPDATE statement works. Click Here to download SQL script to create this table.

FIRST_NAMELAST_NAMESALARY
StevenKing24000
NeenaKochhar17000
LexDe Haan17000
AlexanderHunold9000
BruceErnst6000

Example – 1
Let’s update Last_Name of Steven from King to Feuerstein.

Execute above SQL code and SELECT the table to see the results.

FIRST_NAMELAST_NAMESALARY
StevenFeuerstein24000
NeenaKochhar17000
LexDe Haan17000
AlexanderHunold9000
BruceErnst6000

You can see in the above output that rest all the records are same as before but Last_Name has been replaced with Feuerstein for the record where First_Name is Steven.

Example – 2
Let’s alter this table and add DEFAULT value Oracle to Last_Name column and try to update Last_Name column with DEFAULT keyword and increase Salary by 1000.

When you execute above SQL codes, you will see some changes as below.

FIRST_NAMELAST_NAMESALARY
StevenFeuerstein24000
NeenaOracle18000
LexDe Haan17000
AlexanderHunold9000
BruceErnst6000

We can also update any table based on the values available in any column of another table. But that’s a tapic of another interesting blog.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

Your email address will not be published. Required fields are marked *