Three different statements:

• Adding new rows - two different forms of INSERT statement
• Modifying the value of particular columns in existing rows via the UPDATE statement
• Deleting one or more rows with the DELETE statement

Deleting rows: DELETE

Care has to be taken when deleting. Proper parameters have to be set via a WHERE statement to ensure that only those rows targeted are deleted.

It would be very easy to delete all rows through inappropriate use of the DELETE statement.

DELETE FROM mod_staff                       //Deletes all the rows


DELETE FROM mod_course
WHERE quota IS NULL                         //Deletes only rows with a null quota

DELETE when used with WHERE specifies what should be deleted. If there is no WHERE all rows will be deleted.

Adding rows to a table: INSERT

Values can be inserted directly into a table as a collection:

INSERT INTO mod_staff                       
VALUES ('c5', 'Psychology', 30, 50)       //Note use of brackets and single-quotes


INSERT INTO mod_staff                       
VALUES ('c5', 'Psychology', NULL, NULL)   //Note use of NULL - no quotes

• If column names are not specified, the sequence of values follows the sequence of columns specified when table was created
• If a character exceeds the defined length it will be truncated
• NULL can only be specified if column is not defined as NOT NULL nor as a primary key. Primary keys cannot be NULL
• Data types of values and columns must be compatible

Columns can be specified in the INSERT statement:

INSERT INTO mod_course (title, course_code)    //Note columns defined in brackets                       
VALUES ('Music', 'c8')                                   //Must match data types and column numbers specified

If the number of columns specified and the number of values specified do not correspond the statement will be rejected. Incorrect data types or incorrect use of NULL are also reasons for statement rejection.

Default Values

SQL can insert NULL values into a table column if no value is specified. This is done by default. Such a default cannot be applied if the column is defined as NOT NULL (or primary key). For this reason columns that are defined as NOT NULL must be included in any INSERT statement.

Specified default values can be created at the table creation stage.

Inserting data from other tables

Data can be copied to a target table from another (source) table using INSERT. This source data can also be manipulated by expressions, grouping and functions for the target table.

Data types must be consistent between corresponding columns.

INSERT INTO mod_city         
  SELECT *                  //Selects all the column contents 
  FROM city                 //from city
  WHERE name IN ('Athens', 'Bonn', 'London', 'Paris)

Columns can also be specified from the source:

INSERT INTO mod_staff (region, staff_no)        
  SELECT region, staff_no
  FROM staff                  
  WHERE region = '4'

Source column names and target column names can be different. The important thing is the data types must be correct. There is a drawback. Although the data types may be correct, the data may be incorrect in context to other data in the target table:

INSERT INTO mod_staff (student_id, counsellor_code)        
  SELECT course_code, credit
  FROM course                 
  WHERE credit = 30

In the above the data types are correct between the source and target columns, but the student_id and course_code have different meaning. The same applies for counsellor_code and credit. The data is meaningless in context with other data present.

Modifying rows in a table: UPDATE

Individual rows can be specified for updating. Columns from such rows can also be specified:

UPDATE mod_country
SET capital = 'Paree'              //specified column
      population = 55170             //specified column
      deaths = 20*(cars/area)        //specified columns including those specified for the expression
WHERE name = 'France'              //specified row

Using the above only the row named France specified by the WHERE statement is updated. Only the columns specified in that row are updated. Any other rows/columns are unaffected. The expression applied to the deaths column uses the constants from other columns.

All rows in a specified table can be updated for specified column(s):

UPDATE mod_course
SET credit = credit * 2              //specified columns including same column specified for the expression
      quota = NULL             //specified column

In this statement there is no WHERE clause. The credit column in the complete table is updated. The credit column value is also used by the expression for the updating.

Comments, suggestions, ideas to
Stuart Banner