How to update table columns that are stored as rows?

user_16381542 0
Viewed 83

I am first time trying to update plugin data in wordpress database. I am using Advanced CF7 DB plugin for storing contact form 7 data. For updating the column I dont know whether pivot is required or there is anyother way to achieve this. Have searched but found two tables using pivot. I want to update data in same table just the columns are in rows. Dont know how to do this. Data is stored in below format:

  | id | cf7_id | data_id | name            | value       
  +----+--------+---------+-----------------+------------------
  |  9 |   4561 |       2 | uid             | 60eabb2c20021    
  | 10 |   4561 |       2 | emailId         | axxxxx@gmail.com 
  | 11 |   4561 |       2 | clicked_on_link | No               
  | 12 |   4561 |       2 | orderNos        | Miscommunication 
  | 13 |   4561 |       2 | submission_id   | 177              
  +----+--------+---------+-----------------+------------------

I want to update column clicked_on_link= Yes where uid= 60eabb2c20021. uid will be passed from url parameter.

 | 11 |   4561 |       2 | clicked_on_link | Yes

I want this query in php-sql form so that I can include this in code.I have tried following query which I know is wrong but tried atleast:

update wp_cf7_vdata_entry as b1,
(select data_id,MAX(CASE WHEN name='uid' THEN value ELSE NULL END) AS 
uid,
MAX(CASE WHEN name='email_id' THEN value ELSE NULL END) AS email_id,
MAX(CASE WHEN name='clicked_on_link' THEN value ELSE NULL END) AS 
clicked_on_link 
from wp_cf7_vdata_entry group by data_id) as b2 
set b2.clicked_on_link='Yes'
where b1.data_id=b2.data_id 
and b2.uid=60ea1e95190ee;
wordpress sql php
1 Answers

You can use join:

update wp_cf7_vdata_entry e join
       wp_cf7_vdata_entry e2
       on e.data_id = e2.data_id
    set e2.value = 'yes'
    where e.name = 'uid' and e.value = '60eabb2c20021' and
          e2.name = 'clicked_on_link';

Note: I'm not sure if cf7_id is relevant for the join condition, but your code only uses data_id.

user_11440350
Related