summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_dialect.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-12-12 15:42:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-12-12 15:42:33 +0000
commit579e404567dfff42e64325a8c79f03ac627ea341 (patch)
tree12d101aa5d1b70a69132e5cbd3307741c00d097f /tests/dialects/test_dialect.py
parentAdding upstream version 10.1.3. (diff)
downloadsqlglot-579e404567dfff42e64325a8c79f03ac627ea341.tar.xz
sqlglot-579e404567dfff42e64325a8c79f03ac627ea341.zip
Adding upstream version 10.2.6.upstream/10.2.6
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_dialect.py')
-rw-r--r--tests/dialects/test_dialect.py36
1 files changed, 36 insertions, 0 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 6033570..ee67bf1 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1318,3 +1318,39 @@ SELECT
"BEGIN IMMEDIATE TRANSACTION",
write={"sqlite": "BEGIN IMMEDIATE TRANSACTION"},
)
+
+ def test_merge(self):
+ self.validate_all(
+ """
+ MERGE INTO target USING source ON target.id = source.id
+ WHEN NOT MATCHED THEN INSERT (id) values (source.id)
+ """,
+ write={
+ "bigquery": "MERGE INTO target USING source ON target.id = source.id WHEN NOT MATCHED THEN INSERT (id) VALUES (source.id)",
+ "snowflake": "MERGE INTO target USING source ON target.id = source.id WHEN NOT MATCHED THEN INSERT (id) VALUES (source.id)",
+ "spark": "MERGE INTO target USING source ON target.id = source.id WHEN NOT MATCHED THEN INSERT (id) VALUES (source.id)",
+ },
+ )
+ self.validate_all(
+ """
+ MERGE INTO target USING source ON target.id = source.id
+ WHEN MATCHED AND source.is_deleted = 1 THEN DELETE
+ WHEN MATCHED THEN UPDATE SET val = source.val
+ WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)
+ """,
+ write={
+ "bigquery": "MERGE INTO target USING source ON target.id = source.id WHEN MATCHED AND source.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)",
+ "snowflake": "MERGE INTO target USING source ON target.id = source.id WHEN MATCHED AND source.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)",
+ "spark": "MERGE INTO target USING source ON target.id = source.id WHEN MATCHED AND source.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)",
+ },
+ )
+ self.validate_all(
+ """
+ MERGE INTO target USING source ON target.id = source.id
+ WHEN MATCHED THEN UPDATE *
+ WHEN NOT MATCHED THEN INSERT *
+ """,
+ write={
+ "spark": "MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE * WHEN NOT MATCHED THEN INSERT *",
+ },
+ )