Data Manipulation Language

The next step after creating a table is adding data, in the form of rows, to the table. This is done with the insert into TABLE(...) values (...) statement.

For the table Supplier(sId: integer {PK},name: char(40) {not null},city: char(30) {not null},address: char(100) {not null}), it could look like this:

      insert into Supplier(sId,name,city,address) values
        (1,'Smith','London','11 Waterfront Dr'),
        (2,'Borgaiev','Budapest','Bevásárló u. 24'),
        (3,'Wells','San Diego','272 Wilshire Blvd');

Now the Supplier table contains three rows with the given values. You should specify every column of a row (and insert null if allowed and you don't know the value right now) because unspecified values are inserted along with the given values anyway (and set to null), maybe without you knowing it. If we added another supplier with sId 2, the DBMS would give an error message: it is not allowed to have two rows with the same primary key in one table.

To update some part of the table, you use the update TABLE set ... statement. This statement can be expanded by a where clause which limits the affected rows according to a logical expression. It makes sense to limit by the table's primary key if you want to change one specific row. If we want to change Smith's address:

      update Supplier set address = '12 Waterfront Dr'
        where sId=1;

This will change the address field of any matching row to the given value. In this case, only one row will be affected.

To delete rows from a table, you use the dangerous delete from TABLE statement. This should definitely be supplied with a where clause, or else it will simple delete all rows from the given table! Let's say we want to delete Wells from our supplier list:

      delete from Supplier
        where sId=3;

It's rather simple to insert, update and delete rows in this way. Now the final step in the data cycle is the selection and viewing of values, which will be explained in the next part.