Custom query: Delete submission from given date

If you need to remove all data submitted before e.g. Feb 19th 2014, you can use the below query to look up the number of submissions. Replace @nid with the webform’s node id or set @nid with a SET @nid=<nid>; statement. Also don’t forget to edit the date to your needs.

SELECT sid FROM webform_submissions
WHERE submitted < UNIX_TIMESTAMP('2014-02-19') AND nid=@nid

Webform keeps the submitted user data in 2 tables 'webform_submissions' and 'webform_submitted_data' (former is parent table and the latter holds the individual field submission)

So we modify the SELECT query to JOIN in the data table.

SELECT sid,cid FROM webform_submissions AS t1 LEFT JOIN webform_submitted_data
AS t2 USING (sid,nid) WHERE submitted < UNIX_TIMESTAMP('2014-02-19') AND nid=@nid

The result should be almost the same, just one more row for each submission and component.

Now we modify the SELECT query to DELETE all the occurrences found:

DELETE t1,t2 FROM webform_submissions AS t1 LEFT JOIN webform_submitted_data
AS t2 USING (sid,nid) WHERE submitted < UNIX_TIMESTAMP('2014-02-19') AND nid=@nid

The table aliases need to be stated between DELETE and FROM, otherwise only the first table’s entries would be deleted.

Just run the first query again to confirm the data is removed.

SELECT sid FROM webform_submissions
WHERE submitted < UNIX_TIMESTAMP('2014-02-19') AND nid=@nid

If you need to periodically remove submissions older than a given a amount of time, e.g. 7 days, replace UNIX_TIMESTAMP('2014-02-19') with UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))