CODEWITHSUNDEEP
mysqlif-statementconditional-statements
Moe
Moe

Reputation: 19

MySQL IF function with multiple expressions

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:

id value report_id
1 yes 1001
1 no 1002
1 yes 1003
2 1001
2 1002
3 cat 1001
5 1002

Upvotes: 0

Views: 102

Answers (3)

Moe
Moe

Reputation: 19

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';

Upvotes: 0

Sreek521
Sreek521

Reputation: 669

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;

Upvotes: 1

Barmar
Barmar

Reputation: 781944

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'

Upvotes: 1

Related Questions