MySQL IF function with multiple expressions

Question

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

report_id LIKE report_id will always be true -- everything is always like itself. What are you trying to do with that? — Barmar
– Barmar, Commented Sep 22, 2022 at 19:51
If you're trying to compare report_id between two different rows, you need to join the table with itself. — Barmar
– Barmar, Commented Sep 22, 2022 at 19:53
I am trying to compare report_id between two different rows, making sure they are identical. Thank you! — Moe
– Moe, Commented Sep 22, 2022 at 20:13

Barmar · Accepted Answer · 2022-09-22 19:56:03Z

1

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'

answered Sep 22, 2022 at 19:56

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sreek521 · Accepted Answer · 2022-09-22 19:57:48Z

1

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;

answered Sep 22, 2022 at 19:57

Sreek521

6695 silver badges10 bronze badges

Comments

Siddhartha Mukherjee · Accepted Answer · 2022-09-28 02:25:33Z

0

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

edited Sep 28, 2022 at 2:25

Siddhartha Mukherjee

2,9712 gold badges27 silver badges30 bronze badges

answered Sep 22, 2022 at 22:37

Moe

193 bronze badges

Collectives™ on Stack Overflow

MySQL IF function with multiple expressions

Here are some queries I've attempted so far:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here are some queries I've attempted so far:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related