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

asked 2022-06-13T15:07:39.997+00:00
Peter Ott 1 Reputation point

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,453 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2022-06-14T09:28:18.767+00:00
    Olaf Helper 25,341 Reputation points

    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.

    No comments

  2. answered 2022-06-14T09:42:00.103+00:00
    Peter Ott 1 Reputation point

    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)"

    No comments