Teradata - Data Manipulation

This chapter introduces the SQL commands used to manipulate the data stored in Teradata tables.

Insert Records

INSERT INTO statement is used to insert records into the table.

Syntax

Following is the generic syntax for INSERT INTO.

INSERT INTO <tablename> 
(column1, column2, column3,…) 
VALUES 
(value1, value2, value3 …);

Example

The following example inserts records into the employee table.

INSERT INTO Employee (
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
)
VALUES ( 
   101, 
    Mike , 
    James , 
    1980-01-05 , 
    2005-03-27 , 
   01
);

Once the above query is inserted, you can use the SELECT statement to view the records from the table.

Insert from Another Table

INSERT SELECT statement is used to insert records from another table.

Syntax

Following is the generic syntax for INSERT INTO.

INSERT INTO <tablename> 
(column1, column2, column3,…) 
SELECT 
column1, column2, column3… 
FROM  
<source table>;

Example

The following example inserts records into the employee table. Create a table called Employee_Bkup with the same column definition as employee table before running the following insert query.

INSERT INTO Employee_Bkup ( 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
) 
SELECT 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate,
   DepartmentNo 
FROM  
   Employee;

When the above query is executed, it will insert all records from the employee table into employee_bkup table.

Rules

The number of columns specified in the VALUES pst should match with the columns specified in the INSERT INTO clause.

Values are mandatory for NOT NULL columns.

If no values are specified, then NULL is inserted for nullable fields.

The data types of columns specified in the VALUES clause should be compatible with the data types of columns in the INSERT clause.

Update Records

UPDATE statement is used to update records from the table.

Syntax

Following is the generic syntax for UPDATE.

UPDATE <tablename> 
SET <columnnamme> = <new value> 
[WHERE condition];

Example

The following example updates the employee dept to 03 for employee 101.

UPDATE Employee 
SET DepartmentNo = 03 
WHERE EmployeeNo = 101;

In the following output, you can see that the DepartmentNo is updated from 1 to 3 for EmployeeNo 101.

SELECT Employeeno, DepartmentNo FROM Employee; 
*** Query completed. One row found. 2 columns returned. 
*** Total elapsed time was 1 second.  
EmployeeNo    DepartmentNo 
-----------  ------------- 
   101             3 

Rules

You can update one or more values of the table.

If WHERE condition is not specified then all rows of the table are impacted.

You can update a table with the values from another table.

Delete Records

DELETE FROM statement is used to update records from the table.

Syntax

Following is the generic syntax for DELETE FROM.

DELETE FROM  <tablename> 
[WHERE condition];

Example

The following example deletes the employee 101 from the table employee.

DELETE FROM Employee 
WHERE EmployeeNo = 101;

In the following output, you can see that employee 101 is deleted from the table.

SELECT EmployeeNo FROM Employee;  
*** Query completed. No rows found. 
*** Total elapsed time was 1 second. 

Rules

You can update one or more records of the table.

If WHERE condition is not specified then all rows of the table are deleted.

You can update a table with the values from another table.