Assuming that you have a database, which contains a table, called books. Assume

Question

Assuming that you have a database, which contains a table, called books. Assume that the books table was created with the DDL create statement below and contains the values listed in the following table. Develop a SQL update statement that will change the cost column of each row in the table, which has a publication date before 10/01/2009 to a value of $0.00. Post your solution to the discussion forum and compare with the solutions of your peers. Discuss different approaches to solving this problem create table books ( Bookld Sequence integer, Title Author ISBN Pub_Date date, Cost integer, char(30) char(30, char(20, decimal(10,2)

Explanation / Answer

The SQL update query is given below:

UPDATE books SET Cost = 0 WHERE Pub_Date < '2009-01-10';

Now, following are the ways to optimize this query:

At enterprise level, we expect volumes to be huge and updates are often part of some scheduled jobs/scripts.

With huge volume of data, Updates comes as a costly operation that involve risk of blocking table for some time in case the update fails (due to fallback journal). It's always better to have data that needs to be updated loaded to a temporary table in required form and inserted back to same table after deleting matching records

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---