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

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

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.
12,772 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,916 Reputation points
    2022-06-14T09:28:18.767+00:00

    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.

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

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

    0 comments