How to update a temporary view on Databricks?

Question

I can create a view using the .createOrReplaceTempView() command.
However when I want to use UPDATE command to manually update certain values based on a condition, I get an error:

Error in SQL statement: AnalysisException: UPDATE destination only supports Delta sources.

There must be a working alternative. Can you suggest anything?

Answer 1

Hello @Michael_Zajac ,

Welcome to the MS Q&A platform.

Databricks only supports updates for delta lake tables. The error message indicates that you try the update on a non-delta-table. So you would have to convert your data source to the delta.

Syntax:  
  
CONVERT TO DELTA table_name [ NO STATISTICS ] [ PARTITIONED BY clause ]  

Example:   
CONVERT TO DELTA database_name.table_name; -- only for Parquet tables  
  
CONVERT TO DELTA parquet.`abfss://******@storage-account-name.dfs.core.windows.net/path/to/table`  
  PARTITIONED BY (date DATE); -- if the table is partitioned  
  
CONVERT TO DELTA iceberg.`abfss://******@storage-account-name.dfs.core.windows.net/path/to/table`; -- uses Iceberg manifest for metadata  

A similar thread has been discussed here

I hope this helps.

Document: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/delta-convert-to-delta

------------------------------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators