©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMariaDB

MariaDB SETVAL SQL Function - Set value for sequence

SETVALSyntax:SETVAL(Sequence, Value [, Reserved] [, Cycle])
Return value:INTEGER
Function type:Sequence function
Function Description

Sets and returns the next "Value" to be returned from a "Sequence".

If the sequence "Value" is invalid, SETVAL() returns NULL.


If "Reserved" is true or 1, the following NEXTVAL() returns the next value after the set "Value". This is the default.

If "Reserved" is false or 0, the following NEXTVAL() returns the value, set with "Value".


If the sequence is cyclically, the "Cycle" can be increased with SETVAL(). Without a "Cycle", the "Value" to set must be greater than the current value in the sequence.

With cycle, either the "Value" to set must be greater than the current value in the sequence, or the cycle must be increased.


Available from MariaDB version 10.3.

Example 1: SETVAL() and NEXTVAL() without specifying "Reserved".

 1
 2
 3
 4
 5

select setval(`my_sequence`,60) as result;
select nextval(`my_sequence`as result;


result
bigint(20)
60
result
bigint(20)
61

Example 2: SETVAL() and NEXTVAL() specifying "Reserved" = true.

 1
 2
 3
 4
 5

select setval(`my_sequence`,60,trueas result;
select nextval(`my_sequence`as result;


result
bigint(20)
60
result
bigint(20)
61

Example 3: SETVAL() and NEXTVAL() specifying "Reserved" = false.

 1
 2
 3
 4
 5

select setval(`my_sequence`,60,falseas result;
select nextval(`my_sequence`as result;


result
bigint(20)
60
result
bigint(20)
60

Example 4: Returning of NULL if "Value" is less than the current value in the sequence.

 1
 2
 3
 4

select setval(`my_sequence`,60,falseas result;


result
bigint(20)
NULL

Example 5: SETVAL() and NEXTVAL() with increment of "Cycle".

 1
 2
 3
 4

select setval(`my_sequence`,1,false,2) as result;
select nextval(`my_sequence`as result;

result
bigint(20)
1
result
bigint(20)
1
The examples were created with the MyWAY SQL manager: Download

How to use the SETVAL() function in MariaDB databases

In MariaDB the SETVAL() function is used to manually set the current value of a sequence object to a new specified value. Passing in the name of the sequence object along with the desired new value, the function returns the newly set value, which can then be used for confirmation or further processing. This is used when the sequence needs to be reset to a specific value or synchronized with other data. Changing the value of a sequence can affect the uniqueness of the generated values, so you should check whether the new value also meets the requirements.
SEC_TO_TIMESECONDSESSION_USERSETVALSHASHA1SHA2

Further MySQL und MariaDB SQL Sequence functions

NEXTVALNEXTVAL(Sequence)
More about NEXTVAL Function

LASTVALLASTVAL(Sequence)
More about LASTVAL Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the SETVAL SQL function: mariadb.com
Updated: 2023-09-24ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer