Can I run update with select in Athena ICEBERG?

Question

Hi,
I'm using Athena Table - ICEBERG type.
I would like to update with Select statement in order to update bunch of records at a time. For example:
`UPDATE skus_market_share_sum1
SET sales_kpis_sales_7d_agg = s.new_values
FROM (SELECT SKU, new_values FROM other_table) s
WHERE skus_market_share_sum1.SKU = s.SKU;`

Currently I'm getting an error about the FROM statement.
Is there any other option to update bunch of records using SQL and ICEBERG?

Answer

Hello,

Update might not be suitable for changing/updating multiple rows in a transaction as Athena Iceberg UPDATE writes Iceberg position delete files and newly updated rows as data files in the same transaction. So, you can try using `merge into` statement for this approach. Details are given **[here](https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-updating-iceberg-table-data.html#querying-iceberg-merge-into)**.

```
MERGE INTO skus_market_share_sum1 m
USING other_table o 
ON o.SKU = m.SKU
WHEN MATCHED THEN 
  UPDATE SET m.sales_kpis_sales_7d_agg = o.new_values;
```

This will perform an update where rows match between the two tables based on the ON condition.

Answer

Could run Merge into with Select, example below

```
MERGE INTO skus_market_share_sum1 t 
USING (
    select *
	from (
                       <>
                    )
                where rn = 1 AND category LIKE '%NNN%'
) s
ON (t.sku = s.sku)
WHEN MATCHED 
    THEN UPDATE 
        SET sales_kpis_sales_7d_agg = s.sales_kpis_sales_7d_agg
```

Can I run update with select in Athena ICEBERG?

Conteúdo relevante