Combine two queries into one in same table (same columns/different rows)

Question

For a messaging system, I'm trying to mark the appropriate to_del and from_del fields for employee 2.

Here is before employee 2 deletes messages:

company_id | to | from | to_del | from_del
    1        4     2       0         0
    1        2     4       0         0

Here is after employee 2 deletes messages:

company_id | to | from | to_del | from_del
    1        4     2       0         1
    1        2     4       1         0

I have to run 2 queries for this to work:

UPDATE messages SET from_del='1' WHERE company_id=1 AND from=2
UPDATE messages SET to_del='1' WHERE company_id=1 AND to=2

Is there anyway to merge these 2 queries into 1 more efficient query? Something like:

UPDATE messages SET from_del='1',to_del='1' WHERE company_id=1 AND (from=2 OR to=2)

This single query's issue is that it marks all 4 delete fields as '1'. Any ideas on how to get a single efficient query to act like the 2 above queries?

Ben · Accepted Answer · 2012-02-02 16:35:27Z

2

Sounds like a job for case...

update messages
   set from_del = case when from = 2 then 1 else from_del end
     , to_del = case when to = 2 then 1 else to_del end
 where company_id = '1'

You're updating columns that don't need to be this way, but you're only performing one update not multiple as you would with multiple queries.

I dislike the use of from and to as column names... It just asking for confusion...

answered Feb 2, 2012 at 16:35

Ben

53.1k36 gold badges133 silver badges156 bronze badges

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

5 Comments

Maverick Over a year ago

Thanks for your input, but what do you mean by 'You're updating columns that don't need to be this way'?

Ben Over a year ago

It means that if you only need to update from_del you're also going to update to_del whether you like it or not, you update it to the same value if it doesn't match your condition, but you still update it.

Maverick Over a year ago

is that the else from_del and else to_del? Do you think it's more efficient to run two queries like above or use the case part?

Ben Over a year ago

The query basically updates each column with itself if there's nothing to do. You will have to change it slightly each time. Whether it's more efficient or not depends entirely on your database. I can only say probably :-). It should definitely be quicker. messages is index on company_id?

Maverick Over a year ago

No index yet. It just sucks that you MUST update the value to itself instead of leaving it alone.

Gary Chambers · Accepted Answer · 2012-02-02 16:34:00Z

0

If you're performing an update that requires two different WHERE clauses, then unfortunately you will have to perform two queries. If you are worried about performance, you could always batch them in a single TRANSACTION.

answered Feb 2, 2012 at 16:34

Gary Chambers

25.9k5 gold badges37 silver badges31 bronze badges

Collectives™ on Stack Overflow

Combine two queries into one in same table (same columns/different rows)

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related