I am new to MySQL statements, so bear with me.

I am working with a single table that contains 3 columns. ID, value, and report ID.

What I am looking to do is update the 'value' to yes of id = 2 WHEN the value is 'yes' for id = 1, and at the same time matching the report id is matching between id 1 and id 2. Apologies if this doesn't make sense, please let me know if I need to clarify better.

Here are some queries I've attempted so far:

UPDATE table
    SET value = CASE WHEN value = 'Yes' AND id = 1 AND report_id LIKE report_id 
THEN 'Yes' ELSE value END
    WHERE (id = 2 or id = 1);

UPDATE table 
SET value = 'Yes'     
WHERE (report_id = report_id     
AND value = 'Yes')     
AND id = 1     
 OR id = 2;

UPDATE table
SET value = IF(value = 'Yes' AND report_id LIKE report_id AND id = 1, 'Yes', '')
WHERE id = 2;

Example Table:

CodePudding user response：

All your conditions are being processed on a single row, not comparing the id=1 rows to the id=2 rows with the same report_id.

You can use a self-join to compare different rows of the table with a relationship.

You don't need CASE, you can select the rows to update using the WHERE conditions.

UPDATE t1
JOIN t2 ON t1.report_id = t2.report_id
SET t1.value = 'yes'
WHERE t1.id = 2 AND t2.id = 1 AND t2.value = 'yes'

CodePudding user response：

If I understood correctly, please try to use something like below to get the result. You need to use the same table in exists condition as well. I hope my answer helps you.

update table t set t.value = 'Yes' where exists ( select 1 from table t1 where 
    t1.value = 'Yes' and t1.id = 1 and t.report_id = t1.report_id) and t.id = 2;

CodePudding user response：

UPDATE table a JOIN table b ON a.report_id = b.report_id SET a.value = 'Yes' WHERE a.id = 2 AND b.id = 1 AND b.value = 'Yes';