CODEWITHSUNDEEP
mysqlsql
Troy Cosentino
Troy Cosentino

Reputation: 4778

increment a value when a row is selected SQL

Is there any way to essentially keep track of how many times a row has been pulled from a SQL table?

For example in my table I have a column count. Every time a SQL statement pulls a particular row (lets call it rowA), rowA's 'count' value increases 1.

Either in the settings of the table or in the statement would be fine, but i cant find anything like this.

I know that I could split it into two statements to achieve the same thing, but I would prefer to only send one.

Upvotes: 2

Views: 543

Answers (1)

Brian Webster
Brian Webster

Reputation: 30855

The best way to do this is to restrict read-access of the table to a stored procedure.

This stored procedure would take various inputs (filter options) to determine which rows are returned.

Before the rows are returned, their counter field is incremented.

Note that the update and the select command share the same where clause.

create procedure Select_From_Table1
  @pMyParameter varchar(20), -- sample filter parameter
as

-- First, update the counter, only on the fields that match our filter
update MyTable set Counter = Counter + 1 
where
  MyFilterField like CONCAT('%', @pMyParameter, '%')  -- sample filter enforcement

-- Now, return those rows
select
  *
from
  MyTable
where
  MyFilterField like CONCAT('%', @pMyParameter, '%')  -- sample filter enforcement

A decent alternative would be to handle it on the application side in your data-access layer.

Upvotes: 2

Related Questions