SQL DELETE records | OutSystems

Robert Johnston

Solved

SQL DELETE records

Question

Can some one help am facing issue while SQL query execution in outsystems.

mentioned below query there will no relationship to transation and RepotDetails table but Reportkey attribute exist and both the value will be equal.

Need to delete the records both table maching report key records and record date should be previous year and previous month based on posting date attribute date.

DELETE {Transaction},{ReportDetail}
FROM
{Transaction}
INNER JOIN {ReportDetail} ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
EXISTS(
SELECT
{Transaction}.[ReportKey]
FROM
{Transaction}
WHERE
{Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
AND {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
)

28 Apr 2020

Abdul quadir Saifee

Solution

Abdul quadir Saifee wrote:

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp

If this help you, please mark this as Solution so others can get benefit from this.

See solution in context

28 Apr 2020

Miguel Verdasca

Champion

If you want to do this in design time, you need to add this:
Exec('com'+'mit')

In runtime, just neet put a output variable.
If you try test the query, needs put test values in second tab.

Let me know if this helps you

Cheers

28 Apr 2020

Daryna Kravets

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

28 Apr 2020

6 replies

Last reply 28 Apr 2020

Show thread

Hide thread

Miguel Verdasca

Champion

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel

28 Apr 2020

Robert Johnston

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel

Hi Miguel,

Added output structure to display records,

Also tried simple delete query as below mentioned

DELETE FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey] in(SELECT {ReportDetail}.[ReportKey] FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey]='nes')

On SQL test output below

'nes' record is exisit in the database. Also executed query in server action didn't work :(

28 Apr 2020

Daryna Kravets

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel

Hi Miguel,

Syntax

DELETE {Transaction},{ReportDetail}

is incorrect. I tested it without contitions and got the same error.

28 Apr 2020

Miguel Verdasca

Champion

Robert Johnston wrote:

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel

Hi Miguel,

Added output structure to display records,

Also tried simple delete query as below mentioned

DELETE FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey] in(SELECT {ReportDetail}.[ReportKey] FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey]='nes')

On SQL test output below

'nes' record is exisit in the database. Also executed query in server action didn't work :(

What is 'nes' ?

try put an input variable, and add this input variable to your sql.. and in query assign "nes" to input variable

28 Apr 2020

Miguel Verdasca

Champion

Daryna Kravets wrote:

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel

Hi Miguel,

Syntax

DELETE {Transaction},{ReportDetail}

is incorrect. I tested it without contitions and got the same error.

Hi Daryna, I have to agree with you. I didn't see well the sql

28 Apr 2020

Robert Johnston

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna Kravets Didn't work :(

28 Apr 2020

Abdul quadir Saifee

Hi Robert,

I am checking your query and will get back to you.Just want to know reportkeyand postingdate data types?

28 Apr 2020

1 reply

28 Apr 2020

Show thread

Hide thread

Robert Johnston

Abdul quadir Saifee wrote:

Hi Robert,

I am checking your query and will get back to you.Just want to know reportkeyand postingdate data types?

Hi Abdul ReportKey data type is Text and Postingdate is Date.

Thanks :)

28 Apr 2020

Miguel Verdasca

Champion

This is an example Robert:

28 Apr 2020

3 replies

Last reply 29 Apr 2020

Show thread

Hide thread

Robert Johnston

Miguel Verdasca wrote:

This is an example Robert:

Thanks for your quick response Mguel :), I'll add input variable and check

28 Apr 2020

Miguel Verdasca

Champion

Robert Johnston wrote:

Miguel Verdasca wrote:

This is an example Robert:

Thanks for your quick response Mguel :), I'll add input variable and check

Welcome! Give a feedback :)

28 Apr 2020

Robert Johnston

Miguel Verdasca wrote:

Robert Johnston wrote:

Miguel Verdasca wrote:

This is an example Robert:

Thanks for your quick response Mguel :), I'll add input variable and check

Welcome! Give a feedback :)

Hi Miguel,

Simple delete query I have tested it is working in run time :), Facing issue on deleting records from two table.

like DELETE FROM {Transaction},{ReportDetail}

29 Apr 2020

Abdul quadir Saifee

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.

28 Apr 2020

3 replies

Last reply 28 Apr 2020

Show thread

Hide thread

Robert Johnston

Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.

Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

{Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
AND {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))

Is there any other query solution :(

28 Apr 2020

Abdul quadir Saifee

Robert Johnston wrote:

Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.

Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

{Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
AND {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))

Is there any other query solution :(

then I need to check. :)

28 Apr 2020

Daryna Kravets

Robert Johnston wrote:

Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.

Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

{Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
AND {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))

Is there any other query solution :(

How about something like this?

28 Apr 2020

Abdul quadir Saifee

SyntaxEditor Code Snippet

Add a new attribute "isDelete" with default valu as 0 in both of the tables and then check with this sql and please let me know if this works:

update {Transaction}
set {Transaction}.[IsDelete]=1
FROM
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
update {ReportDetail}
set {ReportDetail}.[isDelete]=1
FROM
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    
    Delete  from {Transaction} where {Transaction}.[IsDelete]=1
    Delete  from {ReportDetail} where {ReportDetail}.[isDelete]=1

28 Apr 2020

Robert Johnston

Hi Daryna,

There is high volume data in production, ForEach exection will not fail with timout error or any other impact ?

Thanks :)
Robert

28 Apr 2020

Abdul quadir Saifee

try this you need to modify syntex as I havenot checked this :

SyntaxEditor Code Snippet

select  id
into #tempDeleteTransaction 
FROM
(select {Transaction}.[Id] id
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
select id
 into #tempDeleteReportDetails 
FROM
(select {ReportDetail}.[Id] id from
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    )
    Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #tempDeleteTransaction)
    Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select Id from #tempDeleteReportDetails)
    DROP TABLE #tempDeleteTransaction;
DROP TABLE #tempDeleteReportDetails;

28 Apr 2020

1 reply

28 Apr 2020

Show thread

Hide thread

Robert Johnston

Abdul quadir Saifee wrote:

try this you need to modify syntex as I havenot checked this :

SyntaxEditor Code Snippet

select  id
into #tempDeleteTransaction 
FROM
(select {Transaction}.[Id] id
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
select id
 into #tempDeleteReportDetails 
FROM
(select {ReportDetail}.[Id] id from
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    )
    Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #tempDeleteTransaction)
    Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select Id from #tempDeleteReportDetails)
    DROP TABLE #tempDeleteTransaction;
DROP TABLE #tempDeleteReportDetails;

Thanks Abdul I'll check and update you :)

28 Apr 2020

Abdul quadir Saifee

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp

28 Apr 2020

2 replies

Last reply 28 Apr 2020

Show thread

Hide thread

Abdul quadir Saifee

Solution

Abdul quadir Saifee wrote:

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp

If this help you, please mark this as Solution so others can get benefit from this.

28 Apr 2020

Robert Johnston

Abdul quadir Saifee wrote:

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp

If this help you, please mark this as Solution so others can get benefit from this.

Hi Abdul,

Applogize for delay in the response

There is invalid attribute error in code, ID is attribute not exsist in details table.

select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID

Can replace with report key? like below

 (select {Transaction}.[ReportKey] Id, {ReportDetail}.[ReportKey] reportID

Final query will be, please correct if query is wrong.

WITH transactioncte as (select {Transaction}.[ReportKey] Id, {ReportDetail}.[ReportKey] reportID  from {Transaction} 
INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]

WHERE  EXISTS(SELECT {Transaction}.[ReportKey] FROM  {Transaction} WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[ReportKey] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[ReportKey] in (select reportID from  #temp)

 drop table #temp

Executed above code with below error

Thank you :)

28 Apr 2020

Miguel Verdasca

Champion

Hi Abdul, how are you?

It would not be better to let Robert do his tests first, and then decide if any of the answers helped him, and if so, mark it as right or else he learn the answer that he implemented and that helped him.

You don't need to be constantly posting, let alone replying that makes the others that the question can help as you say, get lost with the huge scroll down they have to do!

The important thing is to help, this is not a contest of right answers.

Regards,
Miguel

28 Apr 2020

1 reply

28 Apr 2020

Show thread

Hide thread

Abdul quadir Saifee

Miguel Verdasca wrote:

Hi Abdul, how are you?

You don't need to be constantly posting, let alone replying that makes the others that the question can help as you say, get lost with the huge scroll down they have to do!

The important thing is to help, this is not a contest of right answers.

Regards,
Miguel

Sure Miguel. I am agree with you. But as it was a new learning for me so I was bit excited. Will take care of this. Also is it better to delete previous posts as they are just increasing length of page?

28 Apr 2020

Miguel Verdasca

Champion

Thank you for understanding Abdul.

Robert, if you need anything else, say, test the solutions that have been presented to you here, and see if anything solves your problem. If you need help, ask, surely someone in the community has already gone through your problem and can help you, or even if they haven't, they will help you.

Continuation of good work for all.

28 Apr 2020

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines