Database Fundamentals

Understanding how merge uses key columns

A call to conn:merge{} will either insert new rows in a database table or update existing rows.

How It Works

This depends on whether the VMD file that specifies database tables has any key columns defined:

  • If no key columns are defined, new rows are always inserted. Some applications prefer this behavior, since data is never overwritten.
  • If key columns are defined, Iguana performs a SELECT statement to attempt to find rows that match the values provided in the key columns. If a row exists, an UPDATE operation is performed. Otherwise, an INSERT is performed.

Check for Key Columns using Chameleon

To determine whether a table in a VMD file contains a key column.

  1. Open Chameleon
  2. Double-click the name of the table that you want to examine
  3. Check for any columns with the Key check box selectedIn this case the Id column is the key for the Person table.

Note: To see whether conn:merge{} is performing inserts or updates, run the channel with Debug level logging and examine the log messages that contain SQL statements.

Leave A Comment?