Search code examples
mysqlwordpressgravity-forms-plugin

MySql update a value when there is a match with other value

i try to update specific value in a column when there is a match with an other value.

enter image description here

Database = wpddz_rg_lead_detail

I need to update the 0 (lets say to 5) in the value column (field_number 21 = red circle) if there is a match with field_number 23 value (blue circle).

The value for field_number 23 value (blue circle) is a variable i can pass in to the query

UPDATE wpddz_rg_lead_detail SET VALUE = 100 WHERE field_number = 21

does it right, but i need to check first if my variable == de value from field_number 23

is there anybody how can help me with de query?

EDIT & Solution:

Thx @Alex

UPDATE wpddz_rg_lead_detail AS a
JOIN wpddz_rg_lead_detail AS b
  ON a.lead_id = b.lead_id
  AND a.form_id = b.form_id
  AND b.field_number = 23
  AND b.value = '0e71f6d7abc70b41704faf98302a1d689a4cd80a' 
SET a.value = a.value+100
 WHERE a.form_id=2
 AND a.field_number = 21
Solution 
  • UPDATE t t1
JOIN t t2
ON t1.lead_id=t2.lead_id
  AND t1.form_id = t2.form_id
  AND t2.field_number = 23
  AND t2.value IS NOT NULL # here you can set whatever condition for 23 field
SET t1.`value`=5
WHERE 
  t1.lead_id=4
  AND t1.form_id=2
  AND t1.field_number=21