Data Insertion and Modification

Central to all DDL is the simple INSERT statement that inserts data into the database. For examples, see any of the sql scripts other than booksdbddl. The following statement inserts the values specified in the parenthesis after the keyword 'Values' into the corresponding columnname in the list of column names within the parenthesis before values. Here, 14 is entered into authorid, 8 into subjectid and so on.

insert into book(authorid,subjectid,title,price,pubdate)
values (14,8,'
Alice''s Adventures in Wonderland',49.25,'31-DEC-65');

The two single quotes ( Alice''s) are used to escape the single quote. All single quotes within column values should be escaped in this way as oracle considers single quotes to be column delimiters. Also note that we are not specifying a value for the 'bookid' column. If the values of all the columns are specified in the right order, there would be no need to specify the list of column names. Let us use the sequence we created for authorid to add a new author without specifying column names:

insert into author values (author_seq.nextval,'Sample','Author');

The update command modifies existing rows based on values or expressions that occur after the clause 'UPDATE <tablename> SET. The rest of the syntax is virtually identical to that of select statements. Note however that update queries cannot use aggregate functions, joins or order by clauses. The following statement sets the price of a book with id 1 to $50.00:

UPDATE book SET price=50 where bookid=1;

The value to be updated appears after the SET keyword. If you want to update more than one column, you separate them by commas as follows

UPDATE book SET price=50, subjectid=2 where bookid=1;

The delete command deletes rows based on conditions following 'where' statements. The following command deletes all data from the book table. Do not try this out!

DELETE FROM book

The need to update all the values in a column or delete all information from a table is a rare situation. Update and delete statements are more complicated than insert statements because we first have to find the value or set of values that needs to be changed or removed. This is done by using the WHERE clause with its operators, in the same way that we did it in the first part of the book to select data. The following command replaces all single quotes in the book's title column with the oblique quote character. Note that we escape the single quote with another single quote.

UPDATE book SET title=replace(title,'''','`');

Let us fix all author names that start with a lower case character:

UPDATE author SET lastname=initcap(lastname), firstname=initcap(firstname);

Delete the new sample author you created:

DELETE FROM author WHERE LASTNAME like 'Sample%';

Suppose all books that cost over $100 were picked for a 25% discount, the following update would change the concerned rows in orderdetails:

UPDATE orderdetails set discount=25 where unitprice >= 100;