27

I am trying to combine these two queries in twisted python:

SELECT * FROM table WHERE group_id = 1013 and time > 100;

and:

UPDATE table SET time = 0 WHERE group_id = 1013 and time > 100

into a single query. Is it possible to do so?

I tried putting the SELECT in a sub query, but I don't think the whole query returns me what I want.

Is there a way to do this? (even better, without a sub query) Or do I just have to stick with two queries?

Thank You,

Quan

Squall Leohart
  • 657
  • 2
  • 8
  • 20
  • 2
    These are conceptually two different actions. What are you trying to gain by somehow combining them? – Wiseguy Jul 13 '12 at 19:36
  • I am trying to make my query as fast as possible, as it will be handling heavy loads. – Squall Leohart Jul 13 '12 at 19:39
  • 1
    Well, you can't combine these queries. To optimize speed, be sure to index your tables correctly. [`EXPLAIN`](http://dev.mysql.com/doc/refman/5.5/en/using-explain.html) will help inform you of things that need optimizing. – Wiseguy Jul 13 '12 at 19:51
  • If you are under extremely high load and need to further increase speed, consider clustering, what storage engine you use, and the possibility of running queries in batches. – Wiseguy Jul 13 '12 at 19:52
  • thank you. can you elaborate a little bit on clustering? – Squall Leohart Jul 13 '12 at 20:00
  • I'm no expert there, but the basic idea is that you can distribute your database across multiple physical servers, thus partitioning your data and sharing the work among multiple machines. [This](http://www.mysql.com/products/cluster/scalability.html) might give you some info. – Wiseguy Jul 13 '12 at 20:21

6 Answers6

10

This is really late to the party, but I had this same problem, and the solution I found most helpful was the following:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

from https://gist.github.com/PieterScheffers/189cad9510d304118c33135965e9cddb

Tyler
  • 205
  • 2
  • 7
9

Apparently mysql does have something that might be of use, especially if you are only updating one row.

This example is from: http://lists.mysql.com/mysql/219882

UPDATE mytable SET
mycolumn = @mycolumn := mycolumn + 1
WHERE mykey = 'dante';

SELECT @mycolumn;

I've never tried this though, but do let me know how you get on.

AW101
  • 1,620
  • 14
  • 15
4

You can't combine these queries directly. But you can write a stored procedure that executes both queries. example:

delimiter |
create procedure upd_select(IN group INT, IN time INT)
begin
    UPDATE table SET time = 0 WHERE group_id = @group and time > @time;
    SELECT * FROM table WHERE group_id = @group and time > @time;
end;
|
delimiter ;
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 5
    After the UPDATE the rows that Squall Leohart want returned have time = 0, so the SELECT with condition time > @time won't work as expected. – md2perpe Oct 16 '15 at 10:32
3

So what you're trying to do is reset time to zero whenever you access a row -- sort of like a trigger, but MySQL cannot do triggers after SELECT.

Probably the best way to do it with one server request from the app is to write a stored procedure that updates and then returns the row. If it's very important to have the two occur together, wrap the two statements in a transaction.

J. Miller
  • 757
  • 4
  • 9
-1

There is a faster version of the return of updated rows, and more correct when dealing with highly loaded system asks for the execution of the query at the same time on the same database server

update table_name WITH (UPDLOCK, READPAST)
SET state = 1
OUTPUT inserted.
bahrep
  • 29,961
  • 12
  • 103
  • 150
Andrew Zaitsev
  • 323
  • 3
  • 4
-5

UPDATE tab SET column=value RETURNING column1,column2...

PN GH
  • 25
  • 1