SQL II (Insert, Update, Delete Statements)

INSERT

Insert statements allow, as the name suggests, for data to be entered into a table. The basic format for the INSERT statement is:

INSERT INTO [table name] (comma delimted list of column names [optional]) VALUES( [comma delimeted list of values])

You simply need to supply which table you want to work with, which columns in the table you want to add data to, and the values for those columns. The order of the list of columns and values for the columns must match. If a list of column names is not supplied then the database order of the columsn is assumed. Each database engine has its own specifics for how to specify type information for the values, but in general numbers (regarless of precision or format) need nothing special, strings must be enclosed with single brackets, and dates and boolean values differ for each database engine. For MS Access, dates and boolean values can be entered in the same manner as numbers and the engine will convert them automatically (if the column is of the correct type), however for Oracle boolean values do not exist and dates must be converted using the TO_DATE function.

INSERT INTO ORDERS (OrderID, CustomerID, Amount) VALUES(10392, 8492, 29.99)

This statement would insert the three values into the associated columns into the Orders table.

UPDATE

Update statements allow for editing of existing values in a table. An update statement usually uses a where clause to specific which row(s) to update, if a where clause is omitted then all rows in the table will be udated. The update statement follows the following pattern:

UPDATE [table name] SET [comma delimted list of [column name] = [new value]] [WHERE clause]

The Set clause of the update statement is a comma delimited list of [column name] = [value] statements. For example:

UPDATE classes SET RoomNumber = RoomNumber + 100, capacity = 50  WHERE Department = 'CS' AND ClassNumber >= 300

This query would update the room numbers on all 300 and up classes in the CS department to be the current value + 100 and the class capacity to 50 (This might be useful if the room numbers are changed or something like all classes are moved from one floor to another floor where there are bigger rooms).

DELETE

Delete statements remove rows from a table. They basically just define which rows should be deleted (i.e. a where clause) and from which table; following this format:

DELETE FROM [table name] [WHERE clause]

If the where clause is omitted then it will remove all rows from a table.

DELETE FROM Classes WHERE Department = 'CS'

This would delete all CS classes from that table (yea, no more classes!)

Last Updated: 15 April 2005 9:54 AM
Up | Tutorial Main | Home | email author