SUMMARY: This article reviews how to use the basic data manipulation language (DML) types INSERT, UPDATE, UPDATE JOINS, DELETE, and UPSERT to modify data in tables.
- Data Manipulation Language (DML)
- DML Statement Types
- INSERT Statement
- UPDATE Statement
- UPDATE with JOINS
- DELETE Statement
- UPSERT
Data Manipulation Language (DML)
A DML statement is executed when you:
- Add new rows to a table.
- Modify existing rows in a table.
- Remove existing rows from a table.
DML Statement Types
- INSERT
- UPDATE
- DELETE
INSERT Statement
You can add new rows to a table by using the INSERT statement:
Syntax
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
With the above syntax, only one row is inserted at a time.
a) Insert New Rows: Insert new rows for each column.
Table Structure
postgres=# \d departments
Table "public.departments"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
department_id | integer | | |
department_name | character varying(20) | | |
manager_id | integer | | |
location_id | integer | | |
Example
INSERT INTO departments(department_id,department_name,manager_id, location_id) VALUES (10, 'IT', 100, 1100);
Or
INSERT INTO departments VALUES (10, 'IT', 100, 1100);
b) Insert Rows with null values:
Example
INSERT INTO departments(department_id,department_name) values (20,'HR');
Or
INSERT INTO departments values (30,'Sales',null,null);
UPDATE Statement
Using an UPDATE statement a user can modify an existing row.
Syntax
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example 1
Modify a value department id to 50 for an employee whose id is 100 using the WHERE clause::
postgres=# select * from departments ;
department_id | department_name | manager_id | location_id
---------------+------------------+------------+-------------
70 | Public Relations | 100 | 1700
10 | IT | 100 | 1100
(2 rows)
postgres=# update departments set department_id=50 where department_name='IT';
UPDATE 1
postgres=# select * from departments ;
department_id | department_name | manager_id | location_id
---------------+------------------+------------+-------------
70 | Public Relations | 100 | 1700
50 | IT | 100 | 1100
(2 rows)
Example 2
Without the WHERE clause the entire table would get updated:
postgres=# update departments set location_id = 2000;
UPDATE 2
postgres=# select * from departments ;
department_id | department_name | manager_id | location_id
---------------+------------------+------------+-------------
70 | Public Relations | 100 | 2000
50 | IT | 100 | 2000
(2 rows)
Example 3
Update MULTIPLE ROWS.
We can update more than one row using an UPDATE statement:
postgres=# select * from departments ;
department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
10 | IT | 100 | 1100
20 | HR | 110 | 1200
30 | SALES | 130 | 1400
40 | OPERATIONS | 140 | 1600
(4 rows)
postgres=# update departments
SET department_name =
CASE department_id
WHEN 10 THEN 'SALES'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'ACCOUNT'
END
WHERE department_id in (10,20,30);
UPDATE 3
postgres=# select * from departments ;
department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
40 | OPERATIONS | 140 | 1600
10 | SALES | 100 | 1100
20 | RESEARCH | 110 | 1200
30 | ACCOUNT | 130 | 1400
(4 rows)
UPDATE JOINS
We can use UPDATE JOINS to add values from a separate table.
In the below example, we have updated the values in the second table by joining the values from the first table specifying the condition in the WHERE clause.
Update the values in the second table by joining values from the first table:
Create two tables with data:
create table countries (id int, name varchar(20));
create table states (id int, name varchar(20));
insert into countries values (1,'America') , (2,'Brazil') , (3,'Canada') ;
insert into states values (1,'Washington') , (2,'Yardley') , (3,'Zimbabwe') ;
Update the values in the second table by joining values from the first table:
postgres=# UPDATE states
SET Name = countries.Name
FROM countries
WHERE states.ID = countries.ID;
Result:
postgres=# select * from states ;
id | name
----+---------
1 | America
2 | Brazil
3 | Canada
(3 rows)
DELETE Statement
The DELETE statement is used to remove existing rows from a table.
Syntax
DELETE [FROM] table [WHERE condition];
Example 1
Delete rows by restricting a condition using a WHERE clause. If the WHERE clause is omitted, all the rows from the table would be deleted.
postgres=# delete from departments where department_name = 'HR';
Example 2
A subquery will retrieve an output first and then the WHERE condition will be executed:
postgres=# DELETE FROM departments
WHERE department_id = (SELECT department_id FROM departments where location_id=1200);
UPSERT STATEMENT
Using an UPSERT statement, you can update a record if it already exists or insert a new record if it does not. This can be done in a single statement.
Example
Below sample example for usage of INSERT...ON CONFLICT:
postgres=# select * from tab1 ;
pid | sales | status
-----+-------+--------
1 | 20 | CURR
2 | 40 | CURR
3 | 60 | ABS
4 | 30 | NEW
(4 rows)
postgres=# insert into tab1 (sales,status)
values (30,'HOLD')
ON CONFLICT (sales)
DO NOTHING;
INSERT 0 0
postgres=# select * from tab1 ;
pid | sales | status
-----+-------+--------
1 | 20 | CURR
2 | 40 | CURR
3 | 60 | ABS
4 | 30 | NEW
(4 rows)
postgres=# insert into tab1 (sales,status)
values (30,'HOLD')
ON CONFLICT (sales)
DO
UPDATE
SET status = 'PROD';
INSERT 0 1
postgres=# select * from tab1 ;
pid | sales | status
-----+-------+--------
1 | 20 | CURR
2 | 40 | CURR
3 | 60 | ABS
4 | 30 | PROD
(4 rows)
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...