Insert Conflicts

Conflict handling decides whether an insert matching an existing row should fail, be ignored, or update that row. Typical scenarios include replayed jobs, data synchronization, repeated imports, and insert-or-update operations. It is an INSERT execution strategy, not a separate ordinary UPDATE.

Strategies

Strategy	H2 statement	Result
Into	INSERT INTO ... VALUES (...)	The database reports a conflict
Ignore	MERGE ... USING (VALUES ...) ... WHEN NOT MATCHED THEN INSERT	Matched rows remain unchanged; unmatched rows are inserted
Update	MERGE INTO ... KEY (...) VALUES (...)	Matched rows are updated; unmatched rows are inserted

Ignore and Update both use mapped primary-key columns for matching, and H2 executes the complete statement.

Usage

Assume UserInfo maps to this table, with id marked as the entity primary key. Alice already exists:

CREATE TABLE user_info (
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(64),
    age INTEGER
);
INSERT INTO user_info (id, name, age) VALUES (1001, 'Alice', 18);

Submit the same ID with a new name:

UserInfo user = new UserInfo();
user.setId(1001);
user.setName("Bob");
user.setAge(20);

int rows = lambda.insert(UserInfo.class)
        .onDuplicateStrategy(DuplicateKeyStrategy.Update)
        .applyEntity(user)
        .executeSumResult();

With Update, row 1001 becomes Bob, age 20. With Ignore, Alice remains unchanged. Default Into reports the duplicate key.

Use Ignore to preserve matched rows. Omitting the strategy or selecting Into produces a regular INSERT. See Insert Operations for the common API.

Notes

• Ignore and Update require an entity primary-key mapping; dbVisitor does not select another business field.
• Ignore does not suppress every error. Validation and connection failures still fail.
• The result follows H2 MERGE update-count semantics.