Search code examples
mysqlglobal-variablesauto-increment

Simplest way to make a global, auto-incrementing number in mysql

I essentially want to make a 'global variable' in my mysql database.

So I have a table with two fields:

ID
GlobalNumber

And I'm doing this travesty:

UPDATE that_table SET GlobalNumber=GlobalNumber+1 WHERE ID=whatever            
SELECT GlobalNumber FROM that_table WHERE ID=whatever;

But I'm SURE there's a way to do that with a single statement. Anything I try just gives me a syntax error.

How can I do this whole operation in one line?

Solution

  • update and select statement cannot be run together.

    Both update and select statements are two different types of DML (Data Manipulation Language) and cannot be combined together.

    However you can selectively update a few tuples where you can use a nested query.

    Something like this:

    update that_table
set global_variable = global_variable + 1
where ID IN(Select ID from another_table);