Upsert

The upsert statement is a combination of insert and update. If the specified row already exists in the database, it is updated. Otherwise the row is inserted. The type's validation rules are run first, and Delia halts if any fail.

The update statement returns an integer indicating the number of rows affected: 0 means a failure happened, and 1 means the row was inserted or updated.

 let numRowsUpdated = update Customer[1] {name: 'Amy Gonzalez'} 

Upsert using primary key

Use a primary key filter to specify the row.

 type Customer struct {
  id int primaryKey
  firstName string
  lastName string
 } end

 upsert Customer[1] {firstName:'Amy', lastName:'Gonzalez'} //update one row

The fields must contain (at a minimum) all fields except the primary key field.

noUpdate Option

Sometimes you want to insert data if its not in the database, and do nothing if it is. This is known as a soft insert. Use upsert -noUpdate option.

For example, if the database is used to hold application error messages in a type called ErrorMessages. The application administrators can customize the error message text. When the application starts, we want to ensure that there is a row in the table for all known error messages, but we don't want to overwrite any customized messages.

 upsert -noUpdate ErrorMessage[1] {message:'The shopping cart is empty.'}
 upsert -noUpdate ErrorMessage[2] {message:'That product is not available.'}
 //etc