On Duplicate Key Update Feature in H2

on duplicate key update in snowflake

What you are looking for is the MERGE command

https://docs.snowflake.com/en/sql-reference/sql/merge.html

INSERT ... ON DUPLICATE KEY (do nothing)

Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id (it won't trigger row update even though id is assigned to itself).

If you don't care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it's incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE like this:

INSERT IGNORE INTO <table_name> (...) VALUES (...)

Update fields for MySQL Insert ... On Duplicate Key Update

Thanks @Barmar, I appreciate the dialog!

Turns out that since I was trying to avoid this bug: https://bugs.mysql.com/bug.php?id=58637
I tricked myself into creating a primary key that did not work as intended. ;) I had created a composite primary index which was composed of an (auto-increment number, r_id, t_id). Since the auto-increment number was not part of my insert it was allowing the rows to duplicate.
I changed my table indexes so the Primary index is still the auto-increment number and there is a new Unique index which includes (r_id and t_id). This seems to be working now.

I am still curious about part of my original question: When creating the 'on duplicate key update' statement ... Do I really need to include each field that could change? In other words ... Does the statement only evaluate the fields in this statement for changes?

Thanks again @Barmar!

This is my first ever question posted to stackoverflow so hopefully I followed protocol!

JDBC with H2 and MySQL mode: create procedure fails

The "MySQL Compatibility Mode" doesn't make H2 100% compatible with MySQL. It just changes a few things. The documentation lists them:

Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));
When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.
ON DUPLICATE KEY UPDATE is supported in INSERT statements, due to this feature VALUES has special non-standard meaning is some contexts.
INSERT IGNORE is partially supported and may be used to skip rows with duplicate keys if ON DUPLICATE KEY UPDATE is not specified.
REPLACE INTO is partially supported.
Spaces are trimmed from the right side of CHAR values.
REGEXP_REPLACE() uses \ for back-references.
Datetime value functions return the same value within a command.
0x literals are parsed as binary string literals.
Unrelated expressions in ORDER BY clause of DISTINCT queries are allowed.
Some MySQL-specific ALTER TABLE commands are partially supported.
TRUNCATE TABLE restarts next values of generated columns.
If value of an identity column was manually specified, its sequence is updated to generate values after inserted.
NULL value works like DEFAULT value is assignments to identity columns.
Referential constraints don't require an existing primary key or unique constraint on referenced columns and create a unique constraint automatically if such constraint doesn't exist.
LIMIT / OFFSET clauses are supported.
AUTO_INCREMENT clause can be used.
YEAR data type is treated like SMALLINT data type.
GROUP BY clause can contain 1-based positions of expressions from the SELECT list.
Unsafe comparison operators between numeric and boolean values are allowed.

That's all. There is nothing about procedures. As @jccampanero pointed out in the other answer, you must use the syntax specific to H2 if you want to create stored procedures.

Mysql INSERT ... ON DUPLICATE KEY Reinsert with new key

As said in comments, if you are getting collisions with UUID with just a million rows (yes, just) that's because your random uuid generation is using some weak random generator.

Since you now posted your uuid_v4() function, and it relies on MySQL rand(), I can explain why your code is failing.

According to mysql docs: http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html

RAND() is not meant to be a perfect random generator. It is a fast way
to generate random numbers on demand that is portable between
platforms for the same MySQL version.

This means that you can't use mysql to generate your uuid, at least not with RAND() function.

You will need to generate the uuid outside mysql of possible. There's a few libraries for it in many languages:

PHP: https://github.com/ramsey/uuid
Java: https://www.baeldung.com/java-uuid
Javascript: https://www.npmjs.com/package/uuid

Always check if the library you choose uses a Cryptographically-safe random generator.

UPDATE

It's possible to generate safe UUID V4 on MySQL side using random_bytes() function:

This function returns a binary string of len random bytes generated using the random number generator of the SSL library.

So we can update your function to:

CREATE FUNCTION uuid_v4s()
    RETURNS CHAR(36)
BEGIN
    -- 1th and 2nd block are made of 6 random bytes
    SET @h1 = HEX(RANDOM_BYTES(4));
    SET @h2 = HEX(RANDOM_BYTES(2));

    -- 3th block will start with a 4 indicating the version, remaining is random
    SET @h3 = SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3);

    -- 4th block first nibble can only be 8, 9 A or B, remaining is random
    SET @h4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),
                SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));

    -- 5th block is made of 6 random bytes
    SET @h5 = HEX(RANDOM_BYTES(6));

    -- Build the complete UUID
    RETURN LOWER(CONCAT(
        @h1, '-', @h2, '-4', @h3, '-', @h4, '-', @h5
    ));
END

This function should be safe enough to use without care about collisions unless you have a very massive row count.

Test

I've created following test scenario: Insert random UUID v4 as primary key for a table until 40.000.000 rows are created. When a collision is found, the row is updated incrementing collisions column:

INSERT INTO test (uuid) VALUES (uuid_v4()) ON DUPLICATE KEY UPDATE collisions=collisions+1;

The sum of collisions after 40 million rows with each function is:

+----------+----------------+
| RAND()   | RANDOM_BYTES() |
+----------+----------------+
|       55 |              0 |
+----------+----------------+

The number collisions in both scenarios tends to increase as number of rows grows.