Update using JOIN or CTE in Databricks

Question

I am trying to update a delta table in Databricks using the Databricks documentation here as an example. This document talks only about updating a literal value but not using a value from a different table column. Can someone please help me with the right approach for this?

Here is what I'm trying to do:

UPDATE orders AS t1
     SET order_status = ro.order_status
   WHERE EXISTS (SELECT oid, order_status FROM returned_orders AS ro WHERE t1.oid = ro.oid)

OR

WITH CTE
AS
(
    SELECT  oid
        ,   order_status
    FROM    returned_orders
)
UPDATE  orders AS t1
SET     order_status = ro.order_status
WHERE   EXISTS (SELECT oid, order_status FROM CTE AS ro WHERE t1.oid = ro.oid)

Answer 1

If your case is about updating a Dataframe/table from another, you can go with the MERGE syntax. That helped me. And then, I converted them back to DF and updated the DB (for my usecase).