You can use EDB*Loader with a conventional path load to update the rows in 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 doesn't 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. You can't use EDB*Loader to update a partitioned table.
Performing the update
To perform UPDATE, use the same steps as when performing a conventional path load:
Create a data file that contains the rows you want to update or insert.
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 Building the EDB*Loader control file.
Invoke EDB*Loader, specifying the database name, connection information, and the name of the control file. For information about invoking EDB*Loader, see Invoking EDB*Loader.
This example uses the emp table that's distributed with the EDB Postgres Advanced Server sample data. By default, the table contains:
This 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.
The data that's being updated or inserted is saved in the emp_update.dat file. emp_update.dat contains:
Invoke EDB*Loader, specifying the name of the database (edb), the name of a database user and their associated password, and the name of the control file (emp_update.ctl):
Results of the update
After performing the update, the emp table contains:
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).