CODEWITHSUNDEEP
phpmysqlsqlperformancesql-update
Connor
Connor

Reputation: 1855

PHP/MySQL: Combine UPDATE queries?

Stack Overflow. PHP and SQL novice here.

As part of a multi-user private messaging system I've been trying to write to learn how to properly interact with a database through PDO, I have two separate queries that are executed when a user deletes a message, via a single function deleteMessage():

UPDATE messages SET s_deleted = 1 WHERE id = :id AND sender = :sender
UPDATE messages SET r_deleted = 1 WHERE id = :id AND recipient = :recipient

They work well enough to accomplish what I need but running both one after the other, as I am currently doing, doesn't strike me as particularly efficient.

I looked into CASE, but as far as I could understand it wasn't quite what I needed.

Is there a way to combine these two queries so that I'm not peppering my database with extraneous requests? Would I be better off splicing each query into a separate function, i.e., deleteMessageSender() and deleteMessageRecipient()?

Upvotes: 0

Views: 509

Answers (2)

symcbean
symcbean

Reputation: 48357

UPDATE messages 
SET s_deleted = IF(sender = :sender, 1, s_deleted),
r_deleted = IF (recipient = :recipient, 1, r_deleted)
WHERE id = :id AND 
(sender = :sender OR recipient = :recipient);

Upvotes: 1

Barmar
Barmar

Reputation: 780974

UPDATE messages
SET s_deleted = IF(sender = :sender, 1, s_deleted),
    r_deleted = IF(recipient = :recipient, 1, r_deleted)
WHERE id = :id

As you can see, the "trick" is to simply set a column to its existing value when the criteria isn't met, so it only gets updated when necessary.

Upvotes: 1

Related Questions