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 the values keyword. 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,'1965')

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 most SQL variants consider 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 column are specified in the right order, there is no need to specify the list of column names. In the following statement, we do not have to worry about authorid as we have set it to be an automatically incrementing identity field.

insert into author values ('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 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 one out!

DELETE FROM book

There is almost no situation where you would need to update all the values in a column or delete all information from a table. Update and delete statements are more complicated than insert statements because we first have to find the value or set of values we need to change or remove. We do this by using the WHERE clause just the way we did 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,'''','`')

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