Update table in Apache Spark / Databricks using multiple columns

Question

I'm trying to update a table based on a match to another table for multiple columns. I've tried what is shown below but I'm getting the error shown. How is this done?

update my_table set flag = '1' where (patient_id, org) in (
  select distinct (patient_id, org) from enc where lower(enc_type) like '%visit%'
)

Error:

Error in SQL statement: AnalysisException: IN/EXISTS predicate sub-queries can only be used in Filter/Join and a few commands: 'DeltaUpdateTable [post_partum#1911], [1], named_struct

--- EDIT------------------------------

The following is a full example of updating a table based on a complex query that worked based upon the documentation given in the accepted answer.

MERGE INTO events eve
USING (
  select 
    enc.org as org,
    enc.person_id as person_id,
    min(encounter_date) as visit_day
  from 
    enc
    join events eve on enc.org = eve.org and enc.person_id = eve.person_id and eve.is_post_partum = 1
  where 
    lower(enc.enc_type) like '%visit%'
  group by 1, 2
) visits
ON eve.org = visits.org and eve.person_id = visits.person_id
WHEN MATCHED THEN
  UPDATE SET eve.delivery_date = visits.visit_day
;

Raphael K · Accepted Answer · 2020-10-30 00:19:45Z

2

First off, make sure you are using Delta Lake as the table format. Second, I think you are looking for Upserts, which are defined as

An operation that inserts rows into a database table if they do not already exist, or updates them if they do.

To do so you'll need to use MERGE combined with UPDATE. Here's an example with the matching expression:

MERGE INTO events
USING updates
ON events.eventId = updates.eventId
WHEN MATCHED THEN
  UPDATE SET events.data = updates.data
WHEN NOT MATCHED
  THEN INSERT (date, eventId, data) VALUES (date, eventId, data)

See more in the Databricks documentation here.

answered Oct 30, 2020 at 0:19

Raphael K

2,3831 gold badge18 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Thanks, I was able to do what I needed to based on the documentation link you provided.

Collectives™ on Stack Overflow

Update table in Apache Spark / Databricks using multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related