Delete from SQL Server table based on DB temporary view...

Question

I have a situation where I'd like to delete from a SQL server table based on a temporary view. I've created my temporary view and can access the table, but can't create a SQL statement that references both. Any pointers would be appreciated.
Thank you!

Answer 1

based on a temporary view ... but can't create a SQL statement that references bot

What do you mean with "temporary view" and why can't you "reference" them; I guess you mean a JOIN between?

That's to less on information, please post table design as DDL and some more details.

Answer 2

So, I've created a DataLake view as...
%sql

CREATE OR REPLACE TEMPORARY VIEW CHANGED_POS
AS
SELECT EBELN...

I then want to use the results of this view in my SQL delete statement, deleting records from SQL Server table.

I have written the following, but it appears I can't reference CHANGED_POS in my sql statement...

connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.setProperty("Driver", driverClass)

val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)

val stmt = connection.createStatement()
val sql = "delete from LANDING_MDP.ZOPEN_PO where EBELN IN (SELECT EBELN FROM CHANGED_POS)"