SQL MERGE function

[nils-dohop]

I can't find an IBIS version of the SQL MERGE statement: https://www.sqltutorial.net/merge.html is this supported (and I simply can't find the documentation?) or is it currently unsupported by IBIS?

[cpcloud]

We currently do not have an Ibis API for MERGE. Do you have some example SQL code that we could potentially use to inform the API?

[nils-dohop]

For an API design? My use-cases are normally that I have staging tables that I merge into production tables with de-duplication being part of the MERGE.

Something like this in python (for a currencies table):

        query_list = [
            f'MERGE `{target_table}` T',
            f'USING `{staging_table}` S',
            'on T.date = S.date',
            'WHEN NOT MATCHED THEN INSERT (date, ' + ','.join(currencies) + ')',
            'VALUES(date, ' + ','.join(currencies) + ')',
        ]

        query = ' '.join(query_list)

If I were to model this in python I would probably do it as

target_table.merge(source_table, condition, values-when-matched, values-when-not-matched-by-target, values-when-not-matched-by-source)

Or something similar? This is quite hairy.