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?

Yokesh NK · 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.

moshe · 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?

Relevant content