CODEWITHSUNDEEP
sqlhana
user2255207
user2255207

Reputation: 41

SAP HANA: Converting SQL Insert Statement into a delete statement

I have an SQL statement that inserts into a table data from 3 different tables and join them with conditions and I would like to revert the insertion with a DELETE statement (Given also the date as an input value in the delete statement).

INSERT INTO ORD FROM DATASOURCE_ORD INNER JOIN (
  SELECT DISTINCT Field_1, Field_2 FROM INVOICE INNER JOIN (
    SELECT DISTINCT Field_3 FROM FI WHERE DATE >= Input_Value) ON 
      (FI.Field_3 = INVOICE.FIELD_2))A 
      ON (A.Field_4 = DATASOURCE_ORD.Field_5 AND INVOICE.Field_1 = 
           DATASOURCE_ORD.Field_6)

when I try to do it with WHERE EXISTS and keep the A of the second INNER JOIN, it throws an incorrect syntax near A

Any ideas of how I can convert the insert statement into a delete statement

Thank you so much

Upvotes: 0

Views: 900

Answers (2)

Lars Br.
Lars Br.

Reputation: 10396

SQL provides a mechanism to "... revert the insertion ..." - it's called ROLLBACK. Of course, this only works, if the transaction that inserted the data has not been COMMITted yet.

If you want to remove precisely the tuples that were inserted by your INSERT statement, then you need to know the primary keys of those tuples. When you have those keys, it's a matter of DELETE FROM ORD WHERE <primary key> in (<pk1>, <pk2>,...).

But that does not seem to be what the OP is asking for - at least not exactly.

An important point to see here is that it's possible to apply the equivalent filter criteria used for the INSERT for the DELETE operation. However, eventhough the filter is equivalent, it may still affect other tuples than those that were inserted before.

That's because the involved tables can (and likely will) contain data from other transactions. And those other records can easily be included into the filter conditions from the INSERT statement.
In addition, the "target" table does not seem to contain the data that has been used in the filter conditions. Effectively, this created an irreversible mapping between filter conditions and entries in the target table.

Of course, one can still go ahead (and make a mess out of the data) and deleting records from the target table that match (now) whatever "comes out of the filter conditions":

DELETE FROM ORD 
WHERE 
EXISTS
(SELECT * FROM DATASOURCE_ORD DS_ORD
 INNER JOIN (
  SELECT DISTINCT Field_1, Field_2 FROM INVOICE 
  INNER JOIN (
    SELECT DISTINCT Field_3 FROM FI WHERE DATE >= Input_Value) 
   ON 
      (FI.Field_3 = INVOICE.FIELD_2))A 
      ON (A.Field_4 = DATASOURCE_ORD.Field_5 AND INVOICE.Field_1 = 
           DATASOURCE_ORD.Field_6)
WHERE
 (ORD.<col1> = DS_ORD.<col1>, 
  ORD.<col2> = DS_ORD.<col2>, 
   ..)
)

NOTE: While this should technically work it will mess up the data as it does not do, what the OP presumably wanted to achieve: undo the INSERT operation on the target table.

Upvotes: 1

MD H
MD H

Reputation: 37

Deleting means the data has been there in the ORD table, so you don't need to write the inner join in the DELETE Script. just delete directly from ORD table with the field name from ORD table so you don't have to write A. or B. in the Where Clause.

Upvotes: 0

Related Questions