# DBT Merge syntax issue?

**URL:** <https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728>\
**Category:** Help\
**Created:** [June 15, 2023, 4:56pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728 "2023-06-15T16:56:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![msavage0507](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@msavage0507](https://discourse.getdbt.com/u/msavage0507)\
**Post date:** [June 15, 2023, 4:56pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728/1 "2023-06-15T16:56:27Z")

</div>

## I am having a syntax issue with the following dbt merge code. I am new to dbt. this is not a type 2 dimention. My error is

001003 (42000): SQL compilation error:  
16:53:54 syntax error line 84 at position 28 unexpected ‘inserts’.

## Some example code or error messages

```auto
{{
    config(
        materialized='incremental',
        unique_key='test_id',
        incremental_strategy='merge'
    )
}}
with

cte_mgltestordermap as (

    select
        s.testid
        ,s.ordernumber
        ,s.reportmaildate
        ,s.originalreportmaildate
        ,s.preciseflag
        , case
    when production.originalreportmaildate is null then stage.reportmaildate
    else production.originalreportmaildate
  end as upd_originalreportmaildate
        , case
    when production.originalreportmaildate is null then stage.ordernumber
    else production.ordernumber
  end as upd_ordernumber
        ,case
    when production.originalreportmaildate is null then coalesce(existingids.orderid, production.orderid)
    else production.orderid
  end as upd_orderid
        ,case
    when production.preciseflag is null then stage.preciseflag
    else production.preciseflag
  end as upd_preciseflag
  , case when coalesce(p.reportmaildate, '1970-01-01') <> coalesce(s.reportmaildate, '1970-01-01')
    or coalesce(p.preciseflag, 0) <> coalesce(s.preciseflag, 0) then 'update' else null 
      end upd_status     

    from {{ ref('int_mgltestordermap') }} s
    left outer join {{ this }} p on p.testid = s.testid
    left outer join
(
  select orderid, ordernumber
  from {{ this }}
  group by orderid, ordernumber
) existingids on stage.ordernumber = existingids.ordernumber

),

updates as (

    select
        reportmaildate
        ,upd_originalreportmaildate originalreportmaildate
        ,upd_ordernumber ordernumber
        ,upd_orderid orderid
        ,upd_preciseflag preciseflag

    from cte_mgltestordermap

    {% if is_incremental() %}

        where testid in (select testid from {{ this }})
        and upd_status = 'update'  

    {% endif %}

),

inserts as (

    select
    testid
    ,sysdate() recordlastupdated_utc
    ,hash(ordernumber) orderid
    ,ordernumber
    ,reportmaildate
    ,originalreportmaildate originalreportmaildate
    ,preciseflag
    
    from cte_mgltestordermap

    where testid not in (select testid from {{ this }})

)

select * from updates union inserts

```

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [June 15, 2023, 5:05pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728/2 "2023-06-15T17:05:41Z")

</div>

`select * from updates union !!!SELECT * FROM!!! inserts`

Note: `@tom667` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1686848734083809?thread_ts=1686848215.777569&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![msavage0507](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@msavage0507](https://discourse.getdbt.com/u/msavage0507)\
**Post date:** [June 15, 2023, 7:11pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728/3 "2023-06-15T19:11:38Z")

</div>

When I do that I get. 19:10:31 001003 (42000): SQL compilation error:  
19:10:31 syntax error line 84 at position 28 unexpected ‘!’.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [June 15, 2023, 7:57pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728/4 "2023-06-15T19:57:46Z")

</div>

yes, the exclamation points were just for emphasis  
you have to `select` from the second part of the `union`  
do the same thing but remove the exclamation points  
you have to

Note: `@tom667` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1686859060000729?thread_ts=1686848215.777569&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![msavage0507](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@msavage0507](https://discourse.getdbt.com/u/msavage0507)\
**Post date:** [June 16, 2023, 3:09pm UTC](https://discourse.getdbt.com/t/dbt-merge-syntax-issue/8728/5 "2023-06-16T15:09:04Z")

</div>

That worked I am using the dbt merge template and it seems like the doc was not correct.

> **[Merges | dbt Developer Hub](https://docs.getdbt.com/guides/migration/tools/migrating-from-stored-procedures/5-merges)**
>
> dbt has a concept called materialization, which determines how a model is physically or logically represented in the warehouse. INSERTs, UPDATEs, and DELETEs will typically be accomplished using table or view materializations. For incremental...

Also my question is when doing a merge do I have to update the same number of fields as I insert. when I try to update just a few columns it gives me an error because of the union at the bottom. Is there any way to update just say 3 fields but insert 5 for new records?