Teradata Learning - DML Basics (02/22/2013)

DML basically consists of 4 commands:

1. INSERT
    
2. INSERT-- SELECT
    
3. UPDATE
    
4. DELETE

INSERT: Used to Insert a new row in the table.

Example: INSERT INTO employee_table VALUES (121, NULL, 4010, 41211, 'Sukul', 'Mahadik', 890303, 460421, 41000);

In the above example we have entered values for all the columns . However we need not provide values for all the columns as shown below. For the columns we don’t provide a value  will receive either their assigned default value (as provided by the DEFAULT or WITH  DEFAULT options in the CREATE TABLE statement), or a NULL value if no default is specified but nulls are allowed.

Caution: If the column for which we are no providing any value does not accept NULL(defined as NOT NULL) then the insert will fail.

Example: INSERT INTO employee    (last_name, first_name, hire_date,      birthday, salary_amount, employee_number)

VALUES ('Sukul', 'Mahadik', 861027, 541110,76500.00, 1291);

INSERT SELECT: Used to copy rows from one table to another.

Syntax: INSERT INTO target_table SELECT * FROM source_table;

The SELECT portion of the statement may be used to define a subset of rows and/or a subset of columns to be inserted to the target table.

UPDATE: Allows you to modify one or many columns of one or more rows in a table.

The where clause qualifies the rows to be updated.

The where clause of update query can include:

1. Columns names being updated.
2. Subqueries.
3. Joins with columns from other table.

Simple update Example:

UPDATE EMPLOYEE_TABLE
SET SALARY = 1000

WHERE EMPLOYEE_NUMBER= 85746 and EMPLOYEE_NAME='SUKUL;

Update using Subquery:

       

       UPDATE EMPLOYEE_TABLE

SET SALARY = SALARY * 1.10

WHERE DEPARTMENT_NO IN (SELECT DEPTNO FROM DEPARTMENT_TABLE);

Update using Join:

     UPDATE EMPLOYEE_TABLE
     SET SALARY = SALARY * 1.10
     WHERE EMPLOYEE_TABLE.DEPT_NO= DEPARTMENT.DEPT_NO
     AND DEPARTMENT.DEPT_NAME LIKE '%SUPPORT%';

In an update, you can't use the ON clause, so the join condition is specified in the WHERE clause.
 

DELETE :   Allows deleting rows from a table.

If we don’t specify the  where clause all the rows will be deleted.

The where clause of delete query can include:

1. Columns names of the target table.

2. Subqueries.

3. Joins with columns from other table.

Simple Delete example:

DELETE FROM    employee WHERE    department_number = 301;

Deleting all rows:

DELETE FROM  employee ALL; /* Teradata syntax */

DELETE FROM  employee; /* ANSI standard syntax */

Deleting Using Subquery:

DELETE FROM EMPLOYEE_TABLE WHERE DEPARTMENT_NO IN (SELECT DEPTNO FROM DEPARTMENT_TABLE);

Deleting Using Join:

DELETE FROM EMPLOYEE_TABLE

     WHERE EMPLOYEE_TABLE.DEPT_NO= DEPARTMENT.DEPT_NO
     AND DEPARTMENT.DEPT_NAME LIKE '%SUPPORT%';