Developer forum

Forum » Development » A little SQL help needed

A little SQL help needed

E-mail notifications
Hans Ravnsfjall
Hans Ravnsfjall
Reply
Posted on 19/03/2021 16:36:47

Hi

I am trying to delete all form data, older than 30 days. As i see it, a formdata is mainly stored in two tables.

The FormSubmit, and the FormSubmitData. The refernece between the two tables is FormSubmitId in the FormSubmit table and the FormSubmitDataSumbitid in the FormSubmitData table

My query looks like this, but it Doesn´t work.

DELETE f, fd
FROM FormSubmit f
JOIN FormSubmitData fd
ON FormSubmitId = FormSubmitDataSubmitid
WHERE f.FormSubmitDate < GETDATE() - INTERVAL 30 DAY;

 

Anyone got a suggestion on what I am doing wrong in the query above?

 

/Hans

 

 

 


Replies

 
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Posted on 19/03/2021 18:06:07
Reply

You can't delete from two tables at the same time. Instead, you need two queries where the first one deletes the rows that are associated to the rows deleted by the second. Something like this (untested) might work:

DELETE FROM FormSubmitData  WHERE FormSubmitDataSubmitID IN 
(SELECT FormSubmitID FROM FormSubmit WHERE FormSubmitDate < DATEADD(d, -30, GETDATE()))

DELETE FROM FormSubmit WHERE FormSubmitDate < DATEADD(d, -30, GETDATE())

Cheers,

Imar

 
Hans Ravnsfjall
Hans Ravnsfjall
Posted on 20/03/2021 08:11:31
Reply

Brilliant Imar 👍🏻

thank you very mutch 🙏

 

/Hans

 

You must be logged in to post in the forum