Intro to SYBASE: Queries for Modification

Data Modification Queries in SYBASE

The previous section showed the basics of Data retrieval in SYBASE. Now we shall see how to modify data from a database.

After going through the examples below, you should be able to do the following exercises related to your own Database

(a) When an ordered amount of a raw material is received, update it's inventory value.

(b) When a given quantity of a a specific product is assembled, update the inventory position of all its components.

(c) A particular supplier is going ot of business (choose one name from your own database). Update your Database to incorporate this change. Write down what are the important considerations in such a modification. Did you enter the correct constraints for this type of consideration ?

(d) Your marketing group informs you about a new supplier of one particular component. Enter the supplier's information in the database.

(e) When a customer makes an order request, update your database to enter the order details. Also write the query to modify this record when the order is shipped out.

For all the examples, I shall use the materials from the excellent SYBASE Online manual. This set of notes is just a fast way to learn the simple commands. The manuals have complete details.

INSERTing new records in a table.

INSERT into publishers  

VALUES ('1622', 'Jardin, Inc.', 'Camden', 'NJ')

NOTES:

Assumes that a table called publishers was created, with four attribute, in the following sequence: pub_id, pub_name, city, state.
Note that the specification of values is in the same sequence as the attributes of the publishers table.
The values data is surrounded by parentheses, and each value is quoted using either single, or double quotes.

IMPORTANT: It is recommended to use a separate insert command for each row you would like to add to the table.

Sometimes, you may be getting incoming data for your insert command through some computerized source, but in a diffeent order than the sequence of attributes. Also, sometimes you may only know some attribute values, and wish to make a new row with the remaining values as NULL. This can be done as follows:

INSERT into publishers (pub_id, pub_name)  

VALUES ('1756', 'The Health Center')

Which is exactly equivalent in its effect as:

INSERT publishers (pub_name, pub_id)  

VALUES ('The Health Center', '1756')

In place of the values keyword, you may also use a select command to indicate a row. This is nice if you want to create an entry in a table using some information from a different table. The following examples show a couple of neat tricks you can use.

Example 1: using select with the insert command

INSERT publishers  

   SELECT "9999", "test", city, state  

   FROM publishers  

   WHERE pub_name = "New Age Books"

NOTES:
The select part of the query returns a row, which is used to generate the new row in the table publishers.

There is no reason you cannot use any other table to generate the information for the new row. Howevevr, you must ensure that the newly created row does not have some data which violates some constraint specified on your table. Also, it must not have NULL values for attributes which are supposed to be NOT NULL.

One way to avoid this is by using dummy variables in the SELECT commands, as shown in the next example.

Example 2: using select with the insert command

INSERT titleauthor (au_id, title_id) 

  SELECT au_id, "xx1111" 

  FROM authors 

  WHERE au_id NOT IN 

                  (SELECT au_id FROM titleauthor)

Of course, most of the time, you will be only interested in updating some specific value in a table. Such operations are known as update operations. The command to perform the update is also quite straightforward, as will be seen from the following examples.

Example 1. Updates

UPDATE authors  

SET au_lname = "Health", au_fname = "Goodbody"  

WHERE au_lname = "Blotchet-Halls"

If the where clause is not specified, the update will be made on EVERY row of the table:

Example 2: Updates

UPDATE publishers  

SET city = "Atlanta", state = "GA"

The above example will change the publisher address for ALL publishers. This type of change is useful, say, when you have a storewide discount:

UPDATE titles  

SET price = price * 0.95

Finally, you may also change some data using several other tables and selecting specific information from them. This is sometimes useful, but be careful how you use it.

Exmaple 3: Updates using the from clause

UPDATE titleauthor 

 SET title_id = titles.title_id 

 FROM titleauthor, titles, authors 

     WHERE titles.title = "The Psychology of Computer Cooking" 

           AND authors.au_id = titleauthor.au_id 

           AND au_lname = "Stringer"

And finally, you may want to delete some data from your tables. This is done using the delete command. The usage is shown in the examples below.

Example 1: Delete

DELETE publishers  

WHERE pub_name = "Jardin, Inc."

This example shows how to delete one or more rows of a table. As for all other commands, the where clause can be a combination of any logical expression of conditions.

NOTES:

It is important to note that if you omit the where clause from the delete statement, ALL rows of the table will be deleted. In general, this is not the recommended method to clear out a table. The better way is to use the folowing command:

truncate table publishers

Which will delete all rows of the table publishers.

The above commands are sufficient for comomn uses of the Sybase DBMS. Fo rmore complex operations, refer to the SYBASE online manuals.