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, customer WHERE payterms_id_n = cust_paytermsid_fn AND ordhdr_custid_fn = cust_id_n)
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!