MERGE with UPDATE not working as expected?

SQL++
1

This is a follow-up to another forum post: Using MERGE to update matched items from a SELECT

When I run this query:

merge into `travel-sample` 
using (select name from `travel-sample` where name='Texas Wings') as d on key d.name
when matched then update set d.iata='Bush'

It executes successfully (no error). However, it doesn’t seem to actually perform the update. So when I execute this query:

select * from `travel-sample` where name='Texas Wings'

It returns a record, but the “iata” field has not been updated to “Bush” (it’s still “TQ”). What am I missing?

2

update needs to be on target document not source document.

merge into travel-sample p
using (select * from travel-sample where name=‘Texas Wings’) as d on key d.name
when matched then update set p.iata=‘Bush’

1 Like
3

@vsr1 thanks for the response

If I use “select *” in the using, I get an error “Invalid MERGE key of type .”

If I change it to “select name”, then it executes without error, but it still doesn’t seem to update the target document (it remains “TQ” after I try it). Here’s exactly what I’m running:

merge into `travel-sample` p
using (select name from `travel-sample` where name='Texas Wings') as d on key d.name
when matched then update set p.iata='Bush'

and then:

select * from `travel-sample` where name='Texas Wings'
4

Your query did not match any records because d.name is not a part of the Key of document in travel-sample.

In this case you can use direct update instead of merge.

The merge query is.

merge into travel-sample p using (select meta().id from travel-sample where name=‘Texas Wings’) as d on key d.id when matched then update set p.iata=‘Bush’;

merge into travel-sample p using (select d.type||"_"||to_string(d.id) as idx from travel-sample d where d.name=‘Texas Wings’) as d on key d.idx when matched then update set p.iata=‘Bush’;

If you need to use * you need to select all the fileds of the document not the whole document like below.

merge into travel-sample p
using (select d.* from travel-sample d where d.name=‘Texas Wings’) as d on key d.name
when matched then update set p.iata=‘Bush’

1 Like