If...ELSE with subquery in MySQL

Question

in the table 'chat':

enter image description here

I would like to delete messages if they have been deleted by both sender an recipient. If, however, a user is the first to delete a message, this should just be recorded under "deleted_by" with his user id (until the second user also deletes the message).

For message #3 and user #1 my query would look like so:

IF
    (SELECT deleted_by from chat WHERE id=3) <> '1' AND (SELECT deleted_by from chat WHERE id=3) <> '0' THEN
    DELETE FROM chat WHERE id=3
ELSE 
    UPDATE chat
    SET deleted_by=1
    WHERE id=3
END IF

This should delete message #3, but instead I get an error. Can you help me make my according SQLfiddle work?

Thank you!

tsnorri · Accepted Answer · 2014-06-24 12:07:13Z

1

A subquery isn't needed. Instead, you can just delete the row if the other user has already marked it deleted and if it still exists, mark it deleted:

DELETE FROM chat WHERE id = 3 AND deleted_by <> 0;
UPDATE chat SET deleted_by = 4 WHERE id = 3;

answered Jun 24, 2014 at 12:07

tsnorri

2,1395 gold badges22 silver badges32 bronze badges

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

2 Comments

Pingui Over a year ago

Right, thank you! Just for curiosity, though: how could the same result be achived with if...else?

tsnorri Over a year ago

I think you'd have to use a compound statement for that. I haven't done anything with MySQL for a while, so I can't come up with a working example right now. Anyway, see dev.mysql.com/doc/refman/5.7/en/…

Collectives™ on Stack Overflow

If...ELSE with subquery in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related