Increment Column on Select

Question

Evening,

I'm currently running this query:

SELECT `id`, `type`, `controller` FROM `urls` WHERE `url` = :url;
UPDATE `urls` SET `views` = `views` + 1 WHERE `url` = :url;

I run this when a page is loaded and it increments the views column by 1. However, I'm interested to know if there is a way (maybe using something like a trigger) that the view column could be incremented automatically.

AFAIK you are going to have to keep the queries separate. SELECT, then UPDATE. Pretty sure you can't UPDATE on SELECT. — Marcus Recck, May 18 '12 at 02:01
When I run this query: SELECT `id`, `type`, `controller` FROM `urls` WHERE `url` = :url; I'd like the view column of that table to be incremented by 1, as the UPDATE query does, but without having to run the update query. In short: * page load * query urls database for page details * on that query trigger the view column for the selected row to be incremented by 1 @Marcus Recck - The above query works for me. — Kohjah Breese, May 18 '12 at 02:13
Also, so you don't go blind early, get rid of all those unnecessary back-ticks; none of your terms are reserved words, so none of them need escaping with backticks — Bohemian, May 18 '12 at 06:24

score 2 · Accepted Answer · edited May 23 '17 at 10:09

I can't find an exact duplicate but Syntax for "RETURNING" clause in Mysql PDO and Mysql returning clause equivalent give you the answer you need.

MySQL does not have an equivalent of Oracle and PostgreSQLs returning into clause. You'll have to use two separate statements.

If you're expecting to do a lot of updates it might (conditional, it might not) be better to keep the number of views in a separate table, especially as you're not returning the view count in your query. Something like the following:

insert into url_views values(url);

select `id`, `type`, `controller` from `urls` where `url` = :url;

and then if you need the number of views:

select count(*)
  from url_views
where url = :url

or use MySQLs insert ... select syntax.

Increment Column on Select

1 Answers1