Interview Questions   Tutorials   Discussions   Programs   

DB2 - How to update more then one record using update?

asked mar September 9, 2014 12:38 PM  

How to update more then one record using update?


1 Answers

answered By Mswami   0  

UPDATE statement is used to update the data on a table or a view. You can change the value of one or more columns for each row that satisfied the condition.

Updating multiple columns:

Sometimes you might want to update more than one column within one UPDATE statement. For example, imagine the manufacturer has changed the packaging for its product 990, so the dimensions are now 5 ×7 instead of 4 ×6. This update statement synchronizes the database information with the real-world change:

UPDATE product
SET prod_pltwid_n = 5,
    prod_pltlen_n = 7
WHERE prod_id_n = 990

Updating a column in all rows:

Even though updating all table rows is not very typical (and often undesirable), sometimes you might want to perform such an operation. Giving all employees a 5 percent raise, inactivating all customers, setting all column values to NULL — these are a few common examples. As you could have noticed, the keyword here is "all." In other words, we would only want to omit the WHERE clause intentionally if we wanted to update each and every single row in the target table.

The UPDATE statement below increases all product prices by 10 percent (ACME, Inc. struggles with the increased operation costs):

UPDATE product
SET prod_price_n = prod_price_n * 1.1

Update with correlated subquery:

UPDATE order_header 
SET ordhdr_payterms_fn = 
     (SELECT payterms_id_n
      FROM   payment_terms, 
      WHERE  payterms_id_n = cust_paytermsid_fn 
      AND    ordhdr_custid_fn = cust_id_n) 
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!