how to insert/update data in sql database using azure databricks notebook jdbc

Question

I got lots of example to append/overwrite table in sql from AZ Databricks Notebook. But no single way to directly update, insert data using query or otherway.

ex. I want to update all row where (identity column)ID = 1143, so steps which I need to taken care are

val srMaster = "(SELECT ID, userid,statusid,bloburl,changedby FROM SRMaster WHERE ID = 1143) srMaster"
val srMasterTable = spark.read.jdbc(url=jdbcUrl, table=srMaster, 
   properties=connectionProperties)
srMasterTable.createOrReplaceTempView("srMasterTable")
val srMasterTableUpdated = spark.sql("SELECT userid,statusid,bloburl,140 AS changedby FROM srMasterTable")

import org.apache.spark.sql.SaveMode
srMasterTableUpdated.write.mode(SaveMode.Overwrite)
  .jdbc(jdbcUrl, "[dbo].[SRMaster]", connectionProperties)

Is there any other sufficient way to achieve the same.

Note : Above code is also not working as SQLServerException: Could not drop object 'dbo.SRMaster' because it is referenced by a FOREIGN KEY constraint. , so it look like it drop table and recreate...not at all the solution.

Answer 1

Hello @amirkhansinga ,

Thanks for the ask and using the Microsoft Q&A platform .
For writing back to SQL table you will need to use the write method , somethng similar to
srMasterTableUpdated.write which has different mode like overwrite/append etc , read about that more here

In your case , if you try append mode you will not get the error , but then may be that does not suffice the ask .

The reason you are getting thsi error because you have a foreign key relations with some other table . Overwriting mode is basically dropping the table and creating it back with the new set of data in the data frame .
You may try to see this solution and see if it works for you , but I am sure its performance could be very bad .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members