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

Comments

jacquelynfisher’s picture

Thanks for this! It helped develop a query to delete submissions based on the data in a specific field component.

Let's say a webform is the victim of a spam attack. Intermingled in the dozens (possibly hundreds) of spam submissions are genuine submissions you want to keep so clearing the webform is out of the question and manually removing each bad submission would take days you don't have. If the spam submissions contain identical data (say an email address or fake name or something), you can delete these by knowing the nid of the form and the data.

DELETE FROM webform_submissions WHERE nid=1 AND sid in(SELECT sid FROM webform_submitted_data WHERE nid=1 AND data='testvalue'); 

DELETE FROM webform_submitted_data WHERE nid=1 AND sid in( SELECT * FROM (SELECT sid FROM webform_submitted_data WHERE nid=1 AND data='testvalue') AS p );

Change nid=1 to the nid of your webform. Change data='testvalue' to the data you want to use to select bad submissions.

Of course, you should always test this with a SELECT * FROM first to make sure only the "bad" submissions will be selected. We tried it and it worked for one of our webforms in Drupal 6.