Chapter 3 Home | Prev: 3.8 Limiting Results and Paging | Next: Practice Problems

3.9 CRUD - Insert, Select, Update, and Delete

CRUD

CRUD is a database related term that is one of those terms that almost every IT professional should be aware of.  CRUD is a DML term that stands for Create, Read, Update and Delete.  These terms refer to INSERT, SELECT, UPDATE and DELETE keywords in SQL.  Create means Creating a new row of data, selecting data, updating existing data and deleting existing data.  Do not confuse this Create with the CREATE you will learn in Weeks 4 and 5.

Insert, Update and Delete statements are also know as writable or action queries.

Writing Data

For the remainder of week 3 we will move away from Selection and read only statements and focus on writing to the database.  This will include inserting new data rows into the tables, updating existing data rows in tables, and deleting existing rows of data from the database.

3.9.1 INSERT - Inserting New Data Rows

Inserting new rows of data into a database is accomplished using an INSERT statement. In SQL there are 4 forms of the INSERT statement with different features and requirements.

Standard INSERT Form

The standard INSERT statement is formed as:

INSERT INTO <tablename> ( <fieldlist comma separated> )
    VALUES ( <Value list comma separated> );

-- example
INSERT INTO offices (officecode, phone, city, addressline1, state, country, postalcode, territory)
    VALUES (8, '+1 905 555 1212', 'Toronto', '200 Young St. N., 'ON', 'Canada', 'M4A3A1', 'NA');

Notes:

• only those fields that are required (not null) need to be present
• the field list can be in any order
• the number of values must match the number of fields
• the values must be in the same order as the fields they match.
• single quotes are used to indicate string values
• the TO_DATE() function should alwasy be used if hard coded dates are being entered.

Short-Cut INSERT FORM

The short-cut form of the INSERT statement is:

INSERT INTO <tablename> ( <Value list comma separated> );

-- example
INSERT INTO offices VALUES (8, 'Toronto', '+1 905 555 1212', '200 Young St. N., NULL, 'ON', 'Canada', 'M4A3A1', 'NA');

Notes:

• ALL fields values must be included, regardless if they are NULL or not.
• the values MUST be in the same order as the table was created
• the number of values must match the number of fields, even if the Primary Key is an autonumbering sequence
• use the value NULL for fields that are not required and a value is not available

Inserting Multiple Rows in One Statement

It is possible to insert more than one row manually in a single statement.  It is important to note that this statement has a variety of forms in different DBMSs, so if you are familiar with SQL Server, MySQL or other database, Oracle's for is quite different.

INSERT ALL
    INTO <tablename> VALUES ( <value list comma separated> )
    INTO <tablename> VALUES ( <value list comma separated> )
    INTO <tablename> VALUES ( <value list comma separated> )
    SELECT <some field name> FROM <table name>;
    -- note the field and table names in the SELECT do NOT need to from the same table.

-- Example:
INSERT ALL
    INTO offices VALUES (8, 'Toronto', '+1 416 555 1111', '200 Young St. N., NULL, 'ON', 'Canada', 'M4A3A1', 'NA')
    INTO offices VALUES (9, 'Oshawa', '+1 905 555 2222', '155 Simcoe. S., NULL, 'ON', 'Canada', 'N2L3G4', 'NA')
    INTO offices VALUES (10, 'Montreal', '+1 268 555 3333', '1245 Rue Lavac, NULL, 'QC', 'Canada', 'K3S2H4', 'NA')
    SELECT * FROM dual;

notes:

• that there are no commas between the multiple INTO parts
• the standard and short-cut forms can both be used, but typically the shortcut method is used to reduce repetition
• The SELECT statement at the end does not need to be relevant to the data being inserted.  Using the dual table uses a minimal footprint for processing and memory.

INSERT INTO <tablename> VALUES 
    ( <value list comma separated> ),
    ( <value list comma separated> ),
    ( <value list comma separated> );

-- Example:
INSERT INTO offices VALUES 
    (8, 'Toronto', '+1 416 555 1111', '200 Young St. N., NULL, 'ON', 'Canada', 'M4A3A1', 'NA'),
    (9, 'Oshawa', '+1 905 555 2222', '155 Simcoe. S., NULL, 'ON', 'Canada', 'N2L3G4', 'NA'),
    (10, 'Montreal', '+1 268 555 3333', '1245 Rue Lavac, NULL, 'QC', 'Canada', 'K3S2H4', 'NA');

notes:

• that there are mandatory commas between the multiple value sets
• the standard and short-cut forms can both be used, but typically the shortcut method is used to reduce repetition
• There is no extra SELECT statement at the end for MS SQL

Inserting Multiple Rows From another Table

It is possible to insert data into a table using data from another table.  Use this feature with caution as it absolutely will be creating redundent and repetitive data if used incorrectly.  This is typically only used when creating tables through migration of an old database to a new database or importing data into live tables from temporary tables.

    INSERT INTO <tablename> (<fieldlist>) 
        SELECT <fieldlist> 
        FROM <tablename>
        WHERE <condition>         -- optional
        ORDER BY <fieldlist>;     -- optional

Notes:

• the fieldlist in the SELECT must match the fieldlist in the INSERT (not same names, but smae meaning)
• the order of the two fieldlists (SELECT and INSERT) must be the same
• you can choose not to include the fieldlist in the INSERT line, but then must have all fields in the right order in the SELECT line
• The WHERE clause is optional
• The ORDER BY is also optional and not typically needed unless there are autonumbering sequences involved

3.9.2 UPDATE - Updating Existing Data

Updating existing rows is possible through the UPDATE statement. The basic form of UPDATE is:

    UPDATE <tablename> 
        SET <field> = <value>,
            <field> = <value>,
            <field> = <value>,
    WHERE <condition>;   -- optional,

The update statement can update a single field, multiple fields base on the SET part and can update several rows at the same time based on the WHERE part. The optional WHERE part is used in most cases as it is important to very cleary indicate which rows are to be updated. For intance:

UPDATE employees SET lastname = 'Smith';   -- BAD, updates ALL employees

would update every employee in the companies lastname to 'Smith' rather than the one woman who got married on the weekend. OOPS!

UPDATE employees SET lastname = 'Smith'
WHERE lastname = 'Jones';   -- BAD, updates all people with last name Jones to Smith

If the woman is changing her name from Jones to Smith, this statement may seem reasonable, but there is no guarantee that there is not more than one employee with the last name Jones. This statement would change all the Jone's to Smith's. OOPS!

Using the PRIMARY KEY is the best way to clearly indicate the exact row that needs to be updated as by definition it is unique.

UPDATE employees SET lastname = 'Smith'
WHERE employeeId = 343;   -- BEST, Primary Key is unique

3.9.3 DELETE - Deleting Existing Data

Although the most dangerous of the CRUD statements, the DELETE statement is also the simplist.  The basic form of the DELETE statement is:

DELETE FROM <tablename>
WHERE <condition> -- optional 

There is no fieldlist or complexities in the delete statement. It's intention is to delete entire ros and all values in the row(s). If a WHERE clause is not included, this statement will delete all rows from the table indicated; potential disaster.  Therefore, it is highly recommend that in most cases, the WHERE clause is indicated and uses a clear indicator field, such as the Primary Key.