Deletes, Updates, and Upserts ​

In addition to default append (or insert) loads, you can use ybload to delete, update, or "upsert" the rows in a table. You can set the --write-op option to one of the following settings:

• insert: Default "append" behavior occurs.
• delete: Rows in the table that match rows in source files are deleted.
• update: Rows in the table that match rows in source files are updated (column values are modified as needed).
• upsert: Rows in the table that match rows in source files are updated, and rows in the source file that do not already exist in the table are inserted as new rows (appended). This mode combines the ybload insert and update modes into one operation.

Note: When rows are updated, every qualifying row for the update is written to the database, whether the contents of the row changed or not. ybload does not distinguish between matching rows that contain changed column values and matching rows that are exactly the same as existing rows. All updated rows are written to the database. Therefore, it is important to use update and upsert loads only when you know the source data includes a significant number of updated rows (rows in which at least one column value changes).

Deletes, updates, and upserts all depend on key fields for matching target table rows with source file rows, as explained in the following section.

Key Fields ​

The source files for a load are read in order to find rows that should be deleted, updated, or updated and inserted (upserted). One or more designated key fields are used to match table rows to source file records. If the target table for a non-insert load does not have a primary key, the ybload operation must specify one or more key fields explicitly in the command line, using either the --key-field-names option or the --field-defs option. For example:

--key-field-names seasonid, season_name

or:

--field-defs seasonid int primary key

See also Delete, Update, and Upsert Examples and Examples with Duplicate Rows.