SQL – UPDATE Statement part-1

update

admin 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_NAME LAST_NAME SALARY
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000

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_NAME LAST_NAME SALARY
Steven Feuerstein 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000

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_NAME LAST_NAME SALARY
Steven Feuerstein 24000
Neena Oracle 18000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000

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..

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

Translate

Oracle – Translate Function

Hi Geeks, Today, we will talk about an Oracle’s inbuilt function TRANSLATE. TRANSLATE works same as REPLACE function does but […]

Leave a Reply

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