Materialize/test/sqllogictest/ct_upsert.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

statement ok
CREATE TABLE append_only (key INT, val INT)

statement ok
CREATE CONTINUAL TASK upsert (key INT, val INT) ON INPUT append_only AS (
    DELETE FROM upsert WHERE key IN (SELECT key FROM append_only);
    INSERT INTO upsert SELECT key, max(val) FROM append_only GROUP BY key;
)

statement ok
INSERT INTO append_only VALUES (1, 2), (1, 1)

query II
SELECT * FROM upsert
----
1 2

statement ok
INSERT INTO append_only VALUES (1, 3), (2, 4)

query IT
SELECT * FROM upsert
----
1 3
2 4