MYSQL SELECT and INCREMENT in SAME QUERY?

Question

I would like to keep track how many times a row was returned in a mysql query. Is there a way to INCREMENT row data as it is selected? I would like to do this in one shot vs running two queries.

So a rough idea,

SELECT * FROM data WHERE text in($search) & UPDATE hits=hits+1

Something like that.

http://stackoverflow.com/questions/562693/mysql-update-and-select-in-one-pass — user20232359723568423357842364, Jun 12 '13 at 20:39
@user20232359723568423357842364: all of those answers are terrible... But then again, so is the query. :) — Denis de Bernardy, Jun 12 '13 at 20:41
I think you're SOL on MySQL. PostgreSQL will let you do an UPDATE... RETURNING but other than that it will likely need to be handled in your application tier. — Jeremy Holovacs, Jun 12 '13 at 20:42
It's worth reading up on triggers for things like this as well. — Jerry, Jun 12 '13 at 20:43

score 0 · Answer 1 · answered Jun 12 '13 at 20:44

0

SELECT * FROM data WHERE text in($search) & UPDATE hits=hits+1

Assuming hits is in data, you can do this in two queries like so:

UPDATE data
SET hits=hits+1
WHERE text in($search);

SELECT * FROM data WHERE text in($search);

Postgres would allow you to do it in a single statement with a returning * clause on the update statement, but that's about it.

answered Jun 12 '13 at 20:44

Denis de Bernardy

75,850
13
131
154

3

I'd further add that the above can be conducted atomically using transactions. – eggyal Jun 12 '13 at 20:53

MYSQL SELECT and INCREMENT in SAME QUERY?

1 Answers1