DB2 - Data Manipulation Language Statements




DB2 Data Manipulation Language Statements

Data manipulation language (DML) describes the portion of SQL that manipulates or controls data.

Retrieving data using the SELECT statement

The SELECT statement tailors your query to gather data. You can use the SELECT statement to retrieve a specific row or retrieve data in a specific way.

Inserting rows using the INSERT statement

To add a single row or multiple rows to a table or view, use a form of the INSERT statement.

Changing data in a table using the UPDATE statement

To update data in a table or view, use the UPDATE statement.

Removing rows from a table using the TRUNCATE statement

To remove all the rows from a table, use the TRUNCATE statement.

Merging data

Use the MERGE statement to conditionally insert, update, or delete rows in a table or view.

Note - DELETE, INSERT, MERGE, SELECT, and UPDATE are DML statements in the ANSI/ISO standard for SQL, where MERGE can emulate INSERT and DELETE or UPDATE. Although LOAD and UNLOAD resemble DML in their functionality, these DB-Access macros are out-of-scope for most references in this document to "DML statements."

What is DB2 Data Manipulation Language Statements ?

Data Manipulation Language (DML) statements or commands are used for managing data within tables. Some commands of DML are -

Some commands of DML are -

  • SELECT – retrieve data from the a database.

  • INSERT – insert data into a table.

  • UPDATE – updates existing data within a table.

  • MERGE – UPSERT operation (insert or update)

  • CALL – call a PL/SQL or Java subprogram.

  • DELETE – deletes all records from a table, the space for the records remain,

Data Manipulation Language (or DML) consists of SQL statements used to retrieve, insert, update and delete records from database tables. DML statements are the only statements that can be rolled back or recovered.

DELETE

Deletes specified rows from a table. Also has a special format (DELETE WHERE CURRENT OF) for embedded SQL.

INSERT

Inserts a specified number of rows into a table.

SELECT

Retrieves data from one or more tables. Also has a special format (SELECT INTO) for embedded SQL.

UPDATE

Modifies data in specified rows of a table. Also has a special format (UPDATE WHERE CURRENT OF) for embedded SQL.