INCR/DECR Functions

Description

The INCR function increments the column's value given as a parameter for a SELECT statement by 1. The DECR function decrements the value of the column by 1.

Syntax

SELECT [ qualifier ] select_expression

[ { TO | INTO } variable [ {, variable }...; ] ]

...;

select_expression :

 *

 table_name. *

 [expression | counter_expression] [ {, expression |

counter_expression}...]

 

counter_expression :

INCR(path_expression)

The INCR and DECR functions are called "click counters" and can be effectively used to increase the number of post views for a Bulletin Board System (BBS) type of web service. In a scenario where you want to SELECT a post and immediately increase the number of views by 1 using an UPDATE statement, you can view the post and increment the number at once by using the INCR function in a single SELECT statement.

The INCR function increments the column value specified as an argument. Only integer type numbers can be used as arguments. If the value is NULL, the INCR function returns the NULL. That is, a value must be valid in order to be incremented by the INCR function. The DECR function decrements the column value specified as a parameter.

If an INCR function is specified in the SELECT statement, the COUNTER value is incremented by 1 and the query result is displayed with the values before the increment. Furthermore, the INCR function does not increment the value of the tuple affected by the query process but rather the one affected by the final result.

Remark
Example

Suppose that the following three rows of data are inserted into the 'board' table.

CREATE TABLE board (

id  INT, title  VARCHAR(100), content  VARCHAR(4000), read_count  INT );

INSERT INTO board VALUES (1, 'aaa', 'text...', 0);

INSERT INTO board VALUES (2, 'bbb', 'text...', 0);

INSERT INTO board VALUES (3, 'ccc', 'text...', 0);

The following example shows how to increment the value of the 'read_count' column in a data whose 'id' value is 1 by using the INCR function.

SELECT content, INCR(read_count) FROM board WHERE id = 1;

  content                read_count

===================================

  'text...'                       0

In the example, the column value becomes read_count + 1 as a result of the INCR function in the SELECT statement. You can check the result using the following SELECT statement.

SELECT content, read_count FROM board WHERE id = 1;

  content                read_count

===================================

  'text...'                       1