Mark Mark - 5 months ago 15
MySQL Question

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 t1
JOIN wpddz_rg_lead_detail t2
ON t1.lead_id = t2.lead_id
AND t1.form_id = t2.form_id
AND t2.field_number = 23
AND t2.value = '0e71f6d7abc70b41704faf98302a1d689a4cd80a'
SET t1.`value`= 555
WHERE
t1.form_id=2
AND t1.field_number=21

Answer
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