delete IF else UPDATE

[joel24]

i'm trying to either update a messages table or delete the row depending on the result of a select query. this is the logic I want for the operation.

IF (SELECT showInSent FROM messages WHERE m_id = 18) = 1
  THEN UPDATE messages SET showInInbox = 0 WHERE m_id = 18;
ELSE DELETE FROM messages WHERE m_id =18;
END IF;

 

other option is to have 2 queries, though i'd rather do it in 1 if possible?

1- UPDATE messages SET showInInbox=0 WHERE m_id = 18;

2- DELETE FROM messages WHERE showInSent=0 AND m_id=18;

 

Thank you!

 

[Adam]

This kind of 'complex' logic should be within your application logic (PHP), or a MySQL procedure if you use them. Standard SQL isn't capable of altering the flow like that, it's purely for querying data. You'd have to MySQL's extensions (procedures, functions, triggers, etc.) to use flow-altering constructs like that.