Is posible on insert query use auto-increment to concatenate with string for column value?

Question

Is posible on insert query use auto-increment to concatenate with string for one column value?

The current AUTO-INCREMENT is = 89 name columns pkey

Example query string:

INSERT INTO `Tbl` (`ProcessCod`, `ProcessName`, ) VALUES(CONCAT('f-pdf-',AUTO-INCREMENT), 'Text-Description');

All in one query???

update i think on a string like this:

INSERT INTO `Tbl` (
    `ProcessCod`, 
    `ProcessName`)
VALUES(
    SELECT CONCAT('f-pdf-',`AUTO_INCREMENT`) 
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'INFO'
    AND   TABLE_NAME   = 'Tbl',
    'Text-Description'
);

the scope of my question is around the use of auto-increment (no primary key) to fill a column with it and concatenating with a dinamic string not a static prefix.

You could do it with an ON INSERT trigger on the table; but not within the insert statement itself. _But why not just SELECT the concatenation when needed?_ — Uueerdo, May 31 '19 at 18:16
@Uueerdo i think on use the select but not working correclty check update... — walter nuñez, May 31 '19 at 18:26
I meant just concat in normal selects when needed, not insert with a select abusing the information_schema.table. — Uueerdo, May 31 '19 at 18:33
Possible duplicate of [How to make MySQL table primary key auto increment with some prefix](https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix) — sticky bit, May 31 '19 at 19:22
@stickybit it is not the same because: 'p-pdf-' can change depending on the extension. and the column related not is the primary key. — walter nuñez, May 31 '19 at 19:54
[Smart keys are an anitpattern.](https://stackoverflow.com/a/28454136/3404097) — philipxy, May 31 '19 at 21:23

score 0 · Accepted Answer · answered May 31 '19 at 20:40

0

i have solved with this code:

INSERT INTO Tbl (
    ProcessCod, 
    ProcessName
)VALUES(
    (SELECT CONCAT('f-pdf-',`AUTO_INCREMENT`) 
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'INFO'
    AND   TABLE_NAME   = 'Tbl'),
    'Text-Description'
);

it was necessary to place the Select in parentheses so that it works correctly.

answered May 31 '19 at 20:40

walter nuñez

179
1
15

Yes, subselects always have their own parentheses. – philipxy May 31 '19 at 21:24
Sometimes, an idea just sounds bad. – Strawberry Jun 01 '19 at 06:48

Is posible on insert query use auto-increment to concatenate with string for column value?

1 Answers1