DB GEEK

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:

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.

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

UPDATE test_upd
SET
    last_name = 'Feuerstein'
WHERE
    first_name = 'Steven';
    

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

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.

--Alter table to add DEFAULT value to Last_Name column
ALTER TABLE test_upd MODIFY
    last_name DEFAULT 'Oracle';
    
--Update Last_Name as null and increase salary by 1000
UPDATE test_upd
SET
    last_name = DEFAULT,
    salary = salary + 1000
WHERE
    first_name = 'Neena';   
    

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

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!