DML statements

DML (stands for Data Manipulation Language) statements are used to manipulate (add, delete, modify) data. They support transactions. What is a transaction? It means that no matter what changes you do, you can still decide whether you want to commit them or rollback. All changes done during a transaction (session) are visible to that particular user only until committed or rolled back.  There is another feature (command) you can use during transactions and it is SAVEPOINT. As it might be suggested by its name, you can create save points along the way (during the transaction) to which you can rollback in case of need. That means that you can separate/divide a transaction into multiple blocks – you gain better control over the flow.

There are four DML commands

• INSERT
• UPDATE
• DELETE
• MERGE – even though this one is kind of a combination of INSERT / UPDATE; sometimes referred to as UPSERT

INSERT

With INSERT command you can insert data into an existing table. You can choose between inserting a single column or all of them (considering there are no NOT NULL values). If you want to insert only one column you have to specify it in the first part of a query. There might be some restrictions (constraints) on columns which might limit you how or what to insert. Moreover, you have to follow the data types (you cannot insert a string into the number type, etc).

Let’s have a look at some examples.

/*
  let's picture a table with the following structure

   CREATE TABLE sample_table 
   (
     id INT
    ,name VARCHAR2(50)
    ,value VARCHAR2(255)
   );
*/

-- the simplest form
INSERT INTO sample_table (id, name, value)
VALUES (1, 'Johnny', 'Default');

-- if you are sure about the column names and their order you can insert data this way as well
INSERT INTO sample_table
VALUES (1, 'Johnny', 'Default');

-- insert only one column (the rest of the values are auto-converted to NULLs)
InSERT INTO sample_table (id)
VALUES (1);

-- insert with select
INSERT INTO sample_table
SELECT 1
, 'Johnny'
, 'Default'
FROM dual;

INSERT INTO sample_table
SELECT id
, name
, value
FROM sample_table;

INSERT INTO sample_table (id)
SELECT id
FROM sample_table;

UPDATE

With UPDATE you can update (change) data inside an existing table. Rules and conditions are the same as for insert: you cannot mismatch data types, constraints apply, …, If you don’t specify WHERE condition, all rows in a table will be updated!

-- the simplest way (updates all records)
UPDATE sample_table
SET value = 'New value';

-- updates only one records
UPDATE sample_table
SET value = 'New value'
WHERE id = 1;

-- updates multiple records
UPDATE sample_table
SET value = 'New value'
WHERE id BETWEEN 1 AND 10;

-- updates multiple columns
UPDATE sample_table
SET value = 'New value', name = 'Tommy'
WHERE id = 2;

-- using subquery
-- if you do not use the WHERE part, all non-matching ids will be set to NULL in sample_table ... 
UPDATE sample_table st
SET (name, value) = (SELECT at.name, at.value
                         FROM another_table at
                        WHERE st.id = at.id)
WHERE EXISTS (
      SELECT 1
      FROM another_table at
      WHERE st.id = at.id )

DELETE

With DELETE command you can delete records from an existing table. When you use WHERE condition, you can restrict the rows for deletion.

-- the simplest form (deletes all records)
DELETE FROM sample_table;

-- delete selected rows only
DELETE FROM sample_table
WHERE id > 3;

There is one thing that you have to consider while using DELETE. I saw many times people using DELETE/ INSERT to reload a table with fresh data. This is generally a bad concept and you should you TRUNCATE (see DDL statements) instead. Even if you intend to delete just some records from a table when the set of records is bigger than (roughly) 30-40% of a total number of rows in a table do not do that – rather use CTAS (Create Table As Select – see DDL statements). The reason is that DELETE does not reset High Water Mark which wastes time especially on big tables.

MERGE

MERGE statement provides a great tool on how to combine INSERT and UPDATE together in one query. You can update or insert data in an existing table from one or multiple sources and you can easily control how you want the logic to be implemented. This is one of my favorite queries 🙂 One thing to remember is the MERGE is a deterministic operation and that means you cannot update the same row multiple times with the same MERGE statement.

-- template
MERGE INTO target_table t
USING (
        ... source data  / query ... 
      ) s
ON (
     .. merging condition on how the data will be evaluated (whether to insert or update
   )
WHEN MATCHED THEN 
   UPDATE SET 
      ... update rows here ... 
WHEN NOT MATCHED THEN
   INSERT ( ... column list for insertion ... )
   VALUES ( ... value list for insertion ... )
;

-- the simplest way
MERGE INTO sample_table target
USING (
       SELECT a.id
       , m.name
       , m.value
       FROM another_table a
       LEFT JOIN my_table m
       ON a.id = m.id
       WHERE 1=1
       AND m.data = 'New'
     ) source
ON (
     target.id = source.id
      AND
     target.name = source.name
   ) 
WHEN MATCHED THEN
    UPDATE SET target.value = source.value
    DELETE WHERE source.value = 'John' -- optional DELETE
WHEN NOT MATCHED THEN
    INSERT (target.value)
    VALUES (source.value)
    WHERE source.value = 'Danny'
;