MariaDB - setting a next biggest value with UPDATE

Question

I have a table racuni which has a column id_racuna that only has NULL values in it.

This is the table creation query:

CREATE TABLE racuni
(
    id_interesa     INT UNSIGNED    NOT NULL AUTO_INCREMENT,    
    id_racuna       INT UNSIGNED    NULL,
    PRIMARY KEY (id_interesa)
) ENGINE=InnoDB;

As you can see, AUTO_INCREMENT has already been used on the primary key so I can't use it anymore to increment id_racuna. So I tried to use this query:

UPDATE racuni
SET id_racuna = CASE
                    WHEN (SELECT id_racuna FROM racuni ORDER BY id_racuna DESC LIMIT 1) IS NULL THEN 1
                    ELSE id_racuna +1
                END
WHERE id_interesa IN (2);

But I get an error:

I have seen people using aliases to solve this problem but they usualy deal with INSERT statements, so I am kind of lost here. How could I use aliases in my case? In the book from Ben Forta I read that aliases are provided using AS keyword, but people on this forum don't use it...

MySQL does not support more than one auto increment column (q.v. [here](https://stackoverflow.com/questions/7344587/how-do-i-make-another-mysql-auto-increment-column)). — Tim Biegeleisen, Jul 29 '17 at 23:58
@Tim Biegeleisen I know. I also pointed that out in the third paragraph. — 71GA, Jul 30 '17 at 00:00
The first problem with your logic is _which_ `NULL` value get assigned to 1? — Tim Biegeleisen, Jul 30 '17 at 00:03
Well this is needed only for the first non `NULL` value in the column. — 71GA, Jul 30 '17 at 00:11
This sounds like what you need: https://mariadb.com/kb/en/mariadb/create-sequence/ — Julia Leder, Jul 30 '17 at 01:06

score 1 · Accepted Answer · answered Jul 30 '17 at 03:56

1

You seem to want:

set @id := 0;
update racuni r 
    set id_racuna = (@id := @id + 1);

I don't know where the condition on id_interesa comes in.

answered Jul 30 '17 at 03:56

Gordon Linoff

1,242,037
58
646
786

Could you explain this a bit? This is the first time I see `:=`. And why do you say `update racuni r`? – 71GA Jul 30 '17 at 06:22
@71GA . . . That is variable assignment. It is rather important in MySQL/ MariaDB because these databases lack other functionality (such as CTEs and window functions). – Gordon Linoff Jul 30 '17 at 19:42

MariaDB - setting a next biggest value with UPDATE

1 Answers1