The INCR function increases the column's value given as a parameter of the SELECT statement by 1. The DECR function decreases the value of the column by 1.
|Parameters:||column -- the name of column defined with SMALLINT, INT or BIGINT type|
|Return type:||SMALLINT, INT or BIGINT|
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 row(tuple) affected by the query process but rather the one affected by the final result.
If you want to increase or decrease the click counter without specifying INCR or DECR on the SELECT list, specify WITH INCREMENT FOR column or WITH INCREMENT FOR column after the WHERE clause.
SELECT content FROM board WHERE id=1 WITH INCREMENT FOR cnt;
SELECT b.content, INCR(b.read_count) FROM (SELECT * FROM board WHERE id = 1) AS b
SELECT content, INCR(read_count) + 1 FROM board WHERE id = 1;
CREATE TRIGGER event_tr BEFORE UPDATE ON event EXECUTE REJECT; SELECT INCR(players) FROM event WHERE gender='M';
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 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