11.2.8 Updating a Table with a Conventional Path Load

You can use EDB*Loader with a conventional path load to update the rows within a table, merging new data with the existing data. When you invoke EDB*Loader to perform an update, the server searches the table for an existing row with a matching primary key:

•	If the server locates a row with a matching key, it replaces the existing row with the new row.

•	If the server does not locate a row with a matching key, it adds the new row to the table.

To use EDB*Loader to update a table, the table must have a primary key. Please note that you cannot use EDB*Loader to UPDATE a partitioned table.

To perform an UPDATE, use the same steps as when performing a conventional path load:

1.	Create a data file that contains the rows you wish to UPDATE or INSERT.

2.	Define a control file that uses the INFILE keyword to specify the name of the data file. For information about building the EDB*Loader control file, see Section 11.2.3

3.	Invoke EDB*Loader, specifying the database name, connection information, and the name of the control file. For information about invoking EDB*Loader, see Section 11.2.4.

The following example uses the emp table that is distributed with the Advanced Server sample data. By default, the table contains:

edb=# select * from emp;

empno|ename | job | mgr | hiredate | sal | comm | deptno

-----+------+---------+------+--------------------+---------+-------+--------

7369 |SMITH |CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20

7499 |ALLEN |SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |300.00 | 30

7521 |WARD |SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 |500.00 | 30

7566 |JONES |MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20

7654 |MARTIN|SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 |1400.00| 30

7698 |BLAKE |MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30

7782 |CLARK |MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10

7788 |SCOTT |ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20

7839 |KING |PRESIDENT| | 17-NOV-81 00:00:00 | 5000.00 | | 10

7844 |TURNER|SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30

7876 |ADAMS |CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20

7900 |JAMES |CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30

7902 |FORD |ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20

7934 |MILLER|CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10

(14 rows)

The following control file (emp_update.ctl) specifies the fields in the table in a comma-delimited list. The control file performs an UPDATE on the emp table:

LOAD DATA

INFILE 'emp_update.dat'

BADFILE 'emp_update.bad'

DISCARDFILE 'emp_update.dsc'

UPDATE INTO TABLE emp

FIELDS TERMINATED BY ","

(empno, ename, job, mgr, hiredate, sal, comm, deptno)

The data that is being updated or inserted is saved in the emp_update.dat file. emp_update.dat contains:

7521,WARD,MANAGER,7839,22-FEB-81 00:00:00,3000.00,0.00,30

7566,JONES,MANAGER,7839,02-APR-81 00:00:00,3500.00,0.00,20

7903,BAKER,SALESMAN,7521,10-JUN-13 00:00:00,1800.00,500.00,20

7904,MILLS,SALESMAN,7839,13-JUN-13 00:00:00,1800.00,500.00,20

7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1500.00,400.00,30

Invoke EDB*Loader, specifying the name of the database (edb), the name of a database superuser (and their associated password) and the name of the control file (emp_update.ctl):

edbldr -d edb userid=user_name/password control=emp_update.ctl

After performing the update, the emp table contains:

edb=# select * from emp;

empno|ename | job | mgr | hiredate | sal | comm | deptno

-----+------+---------+------+--------------------+---------+-------+--------

7369 |SMITH |CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20

7499 |ALLEN |SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |300.00 | 30

7521 |WARD |MANAGER | 7839 | 22-FEB-81 00:00:00 | 3000.00 |0.00 | 30

7566 |JONES |MANAGER | 7839 | 02-APR-81 00:00:00 | 3500.00 |0.00 | 20

7654 |MARTIN|SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1500.00 |400.00 | 30

7698 |BLAKE |MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30

7782 |CLARK |MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10

7788 |SCOTT |ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20

7839 |KING |PRESIDENT| | 17-NOV-81 00:00:00 | 5000.00 | | 10

7844 |TURNER|SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30

7876 |ADAMS |CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20

7900 |JAMES |CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30

7902 |FORD |ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20

7903 |BAKER |SALESMAN |7521 | 10-JUN-13 00:00:00 | 1800.00 |500.00 | 20

7904 |MILLS |SALESMAN |7839 |13-JUN-13 00:00:00 |1800.00 |500.00 | 20

7934 |MILLER|CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10

(16 rows)

 

 

The rows containing information for the three employees that are currently in the emp table are updated, while rows are added for the new employees (BAKER and MILLS)