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
- MERGE – even though this one is kind of a combination of INSERT / UPDATE; sometimes referred to as UPSERT
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;
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 )
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 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' ;