Search code examples
sqlsql-updatedrupal-webform

Update value of one record with the value of another record in the same table

I've got a tough SQL question for you guys. I am working with the Webform module of drupal.

How this module works:
- every item on the webform is a new record in the 'webform_submitted_data'-table,
- sid = registered user
- cid = webform field

Structure of the table:

NID | SID | CID | NO | DATA

1 --- 168 --- 1 --- 0 --- XXX

1 --- 168 --- 2 --- 0 --- YYY

The problem: - CID 64 should get updated with the value of CID 56 of the same SID.

Solution

  • I think it should be as follows:

    UPDATE webform_submitted_data as a, webform_submitted_data as b 
SET a.data = b.data WHERE a.sid = b.sid AND a.cid = 64 AND b.cid = 56

    Basically, you self-join the table (matching SIDs) and then you have both fields in your query, so you can simply refer to each at once.