MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY( ID ),
                    val INT NOT NULL, UNIQUE( val ),
                    test INT DEFAULT 1,
                    action varchar( 10 ) DEFAULT 'insert',
                    comment varchar( 30 ) );

INSERT INTO test ( val )
VALUES ( 1 ), ( 2 )
ON DUPLICATE KEY UPDATE test = VALUES( test ) + 1, action = 'update';

SELECT * FROM test;


id
val
test
action
comment


1
1
1
insert
null

2
2
1
insert
null


INSERT INTO test (val ) 
VALUES ( 1 ), ( 2 ), ( 3 )
ON DUPLICATE KEY UPDATE test = VALUES( test ) + 1, action = 'update';

SELECT * FROM test;


id
val
test
action
comment


1
1
2
update
null

2
2
2
update
null

3
3
1
insert
null


INSERT INTO test (val ) 
VALUES ( 1 )
ON DUPLICATE KEY UPDATE test = VALUES( test ) + 1, action = 'update', comment = 'Why isn''t test 3?';

SELECT * FROM test;


id
val
test
action
comment


1
1
2
update
Why isn't test 3?

2
2
2
update
null

3
3
1
insert
null