CODEWITHSUNDEEP
mysqlsqlmariadb
broadband
broadband

Reputation: 3488

Set column value of this/next auto_increment value

Is there any way to accomplish below sql result as a one liner.

create table test( id int not null primary key auto_increment, name char(10));

insert into test (name) values ('voda'+ this_value_of_id);

// so select would return
MariaDB [testdb]> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | foo1 |
+----+------+

Yes, I know the other way is 

begin transaction;
insert into test (name) values ('voda');
update test set name = concat('voda', id) where id = 1;
commit;

Upvotes: 0

Views: 84

Answers (1)

wchiquito
wchiquito

Reputation: 16551

An option or approach can be via a Virtual (Computed) Columns.

Example:

MariaDB [testdb]> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> CREATE TABLE `test` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `name` CHAR(10),
    ->   `nameid` VARCHAR(20) AS (CONCAT(`name`, `id`)) VIRTUAL
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> INSERT INTO `test`
    ->   (`name`)
    -> VALUES
    ->   ('foo'), ('voda');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT
    ->   `id`,
    ->   `nameid` `name`
    -> FROM
    ->   `test`;
+----+-------+
| id | name  |
+----+-------+
|  1 | foo1  |
|  2 | voda2 |
+----+-------+
2 rows in set (0.00 sec)

Upvotes: 2

Related Questions