EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials DB2 Tutorial DB2 update

DB2 update

Updated March 13, 2023

DB2 update

Introduction to DB2 update

DB2 provides different types of functionality to users like MySQL; the update statement is one of the functionalities that is provided by the DB2. The update statement is used to modify the specified content of columns from the table. Basically, the update command uses the SET keyword to specify the column name for modification and assign a new specified value for that field that we want. When we use the default, so, at that time, we need to use the default keyword; the update command also uses where clause to specify conditions for the update, and we can also use order by clause to make them in order; the order by clause is optional.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Update specific table name set colmn 1 field = new specified value,  colmn 1 field = new specified value,……….. colmn N field = new specified value

where [Condition.......]

Explanation

In the above syntax, we use an update statement with different parameters as follows.

  1. First, we need to specify the table name that we need to update.
  2. After that, we need to specify the column name that we need to update as shown in the above syntax, column 1 field to up to N.
  3. In the third step, we need to specify the condition using the where clause; if this condition is true, we can successfully update the value; otherwise, it shows the error message.

How DB2 update statement works?

Now let’s see how the update statement works in DB2 as follows.

For the update statement, we need one of the permission for authorizations of ID as follows.

  1. We need to update privileges on specific tables and views.
  2. We also need to update privilege on specific columns.
  3. UPDATE privilege on schema for the specified table.
  4. As well as we also need control privilege on the specified table.
  5. We need database access permission for specific tables.

In the event that a row fullselect is remembered for the task, the privileges held by the approval ID of the statement should incorporate at any rate one of the accompanying experts for each referred to table and view.

  1. We need a SELECT advantage on the specified table.
  2. We need SELECTIN privilege on a schema that contains the specified table.
  3. We also need control privilege.
  4. We need database access privilege on the schema that contains the specified table.
  5. Finally, we also need the database authority.

For every specified table and view that is referred to by a subquery, the privilege held by the approval ID of the statement should likewise incorporate at any rate one of the accompanying specialists.

  1. We need a SELECT advantage on the specified table.
  2. We need SELECTIN privilege on a schema that contains the specified table.
  3. We also need control privilege.
  4. We need database access privilege on the schema that contains the specified table.
  5. Finally, we also need the database authority.

In the event that the package used to handle the statement and they looked through the type of an UPDATE articulation incorporates a reference to a column of the table and view, or moniker in the correct side of the task provision, or anyplace in the pursuit condition, the advantages held by the approval ID of the assertion should likewise incorporate at any rate one of the accompanying specialists:

  1. We need a SELECT advantage on the specified table.
  2. We need SELECTIN privilege on a schema that contains the specified table.
  3. We also need control privilege.
  4. We need database access privilege on the schema that contains the specified table.
  5. Finally, we also need the database authority.

Examples of DB2 update

Now let’s see a different example of an update statement in DB2 as follows.

First, create a new table as follows.

create table company (Comp_Id int not null, comp_name varchar(30) not null,

comp_address varchar(30) not null, primary key(Comp_Id));

Explanation

In the above example, we use a create table statement to create a new table name as a company with different attributes such as Comp_id, comp_name, and comp_address with different data types and different sizes as shown in the above statement. Note here comp_name, and comp_address have varchar data type with size 30 as shown.

For confirmation, insert some records by using the following insert into the statement as follows.

insert into company (Comp_Id, comp_name, comp_address) values(1, "HP", "Mumbai"), (2, "Dell", "Pune"), (3, "LG", "Delhi"), (4, "Bajaj", "Kolkatta"), (5, "Usha", "Kochi");
select * from company;

Explanation

In the above example, we use to insert into a statement to insert new records into the company table. Here we insert string values in the comp_name and comp_address column with minimum length.  The end out we illustrate by using the following screenshot as follows.

Now perform the update operation as follows.

DB2 update output 1

Now suppose we need to update the address of LG company at that time; we can use the following statement as follows.

update company set comp_address = ‘Mumbai’ where Comp_Id = 3 ;

Explanation

In the above statement, we use the update statement; here, we try to update the comp_address from the company table. The end out we illustrate by using the following screenshot as follows.

DB2 update output 2

So in this way, we can perform the update statement as per user requirements with different clauses.

Advantages and disadvantages

Now let’s see the advantages as follows:

  1. We can easily update the specified row as per our requirement.
  2. If any cluster key is updated to the same value, then the operation of the update statement will be logged.
  3. It is a high-speed operation; no extra coding is required to update the operation.
  4. We can update multiple columns by using a single update statement.

Now let’s see the disadvantages as follows:

  1. We need multiple access permission to perform the update statement.
  2. The update statement is not supported by, text driver.
  3. If we omit the where clause, then it will update all records from the specified table.

Conclusion

We hope from this article you learn the DB2 update statement. From the above article, we have learned the basic syntax of the DB2 update statement, and we also see different examples of the DB2 update statement. Furthermore, from this article, we learned how and when we use the DB2 update statement.

Recommended Articles

This is a guide to the DB2 update. Here we discuss the basic syntax of the DB2 update statement, and we also see different examples of the update statement. You may also have a look at the following articles to learn more –

  1. DB2 Translate
  2. DB2 Version
  3. DB2 Data Types
  4. DB2 INSERT
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW