# Insert into from select with conflict update set

**URL:** <https://community.cratedb.com/t/insert-into-from-select-with-conflict-update-set/1509>\
**Category:** CrateDB\
**Created:** [June 14, 2023, 10:50am UTC](https://community.cratedb.com/t/insert-into-from-select-with-conflict-update-set/1509 "2023-06-14T10:50:10Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [June 14, 2023, 10:50am UTC](https://community.cratedb.com/t/insert-into-from-select-with-conflict-update-set/1509/1 "2023-06-14T10:50:10Z")

</div>

Hi all,

This is a sanity check really.

I’m doing an aggregation insert from a select using avg.max and max\_by (downsampling) in the select grouped by a date\_bin.  
I want to do an **on conflict update set** to update the data if the source select data has changed.

Is it correct in the update set to do this :-

I

```auto
NSERT INTO <Table>
 ae1,
 e,
....
( SELECT
    avg( ae1),
    MAX_BY(e,ts),
    ...  
 )
ON CONFLICT UPDATE SET 
          ae1 = avg( excluded.ae1 ),
          e = MAX_BY( excluded.e,timestamp ),
...

```

Is this correct ?

Many thanks  
David

---

<div class="post-metadata">

**Author:** ![hernanc](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hernanc/32/676_2.png) [@hernanc](https://community.cratedb.com/u/hernanc)\
**Post date:** [June 16, 2023, 5:39pm UTC](https://community.cratedb.com/t/insert-into-from-select-with-conflict-update-set/1509/2 "2023-06-16T17:39:17Z")

</div>

Hi David,  
The syntax you are looking for would be something like:

```sql
INSERT INTO <Table> ( ae1, e,....)
SELECT
    avg( ae1) ,
    MAX_BY(e,ts) ,
    ...  
ON CONFLICT (<PKcolumns of Table>) DO UPDATE SET 
          ae1 = excluded.ae1 ,
          e = excluded.e,
		  ... ;

```

The fields in `excluded` are the values that could not be inserted so they are already aggregated, but please do a few tests before putting this into production.  
This approach requires that you still have available all the data to compute the downsampled values for the period.  
An alternative approach would be to keep a “weight” in the table with the downsampled data, that way when a new value is added to the period or a value changes you can amend the downsampled value, but aggregations are so fast in CrateDB that if the data is still available it may not be worth the additional complexity.