Last updated February 25, 2014. Created on February 25, 2014.
Edited by flk. Log in to edit this page.

I am currently using webform to store feedback for different courses that were few weeks apart. Each course has its feedback analyzed straight after.

I sadly forgot to remove the old dataset and users started feeding back. I used the below query to remove the old and leave current data intact:

I needed to remove all data submitted before feb 19th 2014, so I used the below query to look up the number of submissions.

SELECT ws.sid FROM webform_submissions AS ws
WHERE submitted < UNIX_TIMESTAMP(STR_TO_DATE('Feb 19 2014', '%M %d %Y'))

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 need to using our select query delete all the occurrences found:

DELETE FROM webform_submitted_data WHERE sid IN (
SELECT ws.sid FROM webform_submissions AS ws
WHERE submitted < UNIX_TIMESTAMP(STR_TO_DATE('Feb 19 2014', '%M %d %Y'))
);

That was easy enough, now to delete the references.
As you cannot modify the same table you selected from a subquery within the same query, we need to use a little trick to delete the content within the parent table (webform_submissions)

DELETE FROM webform_submissions WHERE sid IN (
SELECT * FROM (
SELECT ws.sid FROM webform_submissions AS ws
WHERE submitted < UNIX_TIMESTAMP(STR_TO_DATE('Feb 19 2014', '%M %d %Y'))

    ) AS p
);

Just run the below query to confirm the data is removed.

SELECT ws.sid FROM webform_submissions AS ws
WHERE submitted < UNIX_TIMESTAMP(STR_TO_DATE('Feb 19 2014', '%M %d %Y'))

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

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.