Concatenating a String and Primary Key Id While Inserting

Concatenating a string and primary key Id while inserting

If user_id is an AUTO_INCREMENT primary key, then you can't do this with a single statement, even if you use a trigger.

The problem is that the AUTO_INCREMENT value isn't generated until after the BEFORE INSERT trigger runs, but you can't change username in the AFTER INSERT trigger.

So you just have to do the INSERT, then immediately do an UPDATE.

If user_id is not an AUTO_INCREMENT, but instead is something you specify yourself, then it's easy, you just do the concatenation in your PHP code before you pass the values as parameters.

---

Update: You can't do it with MySQL 5.7 generated columns either. It results in this error when you try to create the table:

Generated column 'username' cannot refer to auto-increment column.

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

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.

Can i concat the last insert id in the first insert?

Two queries is enough:

INSERT INTO table 
    SET string = 'string_';
UPDATE table 
    SET string = CONCAT(string, id) WHERE string = 'string_';

How can I use the auto increment value (primary key column) as part of another column in the same row when inserting that row?

As others have pointed out, you can programmatically use the ID in the later call, it's probably more efficient.

BUT, let's imagine you still want to do this (maybe you'll join this to other data later, or use it with some non-data savvy folks, whatever).

I'd do this in two commands.

First, run your insert to generate the IDs, and fill in 'folder/' as a static value for the image_address column.

Second, run an UPDATE query against your newly formed table (or if you just added rows to an existing table, you can UPDATE WHERE image_address = 'folder/'), and in this update you can modify the image_address field by concatenating it with the id.

SQL Server : concatenate the primary key ID within a grouped statement

Small tweak to what you already have will get you there.
Give this a try:

DECLARE @Jobs TABLE
    (
        [JobID] INT
      , [ExpressJob] NVARCHAR(100)
      , [ItemID] INT
    );

DECLARE @Items TABLE
    (
        [ItemID] INT
      , [Colour] NVARCHAR(100)
      , [Size] NVARCHAR(100)
    );

INSERT INTO @Jobs (
                      [JobID]
                    , [ExpressJob]
                    , [ItemID]
                  )
VALUES ( 10001, 'true', 3 )
     , ( 10002, 'true', 3 )
     , ( 10003, 'false', 4 );

INSERT INTO @Items (
                       [ItemID]
                     , [Colour]
                     , [Size]
                   )
VALUES ( 3, 'Blue', '1-2' )
     , ( 4, 'Pink', '5-6' );

SELECT     [i].[ItemID]
         , [i].[Colour]
         , [i].[Size]
         , [j].[ExpressJob]
         , COUNT([i].[ItemID]) AS [Quantity]
         --Added '|' as that was how you wanted the results delimited
         , STUFF((
                     SELECT '|' + CONVERT(VARCHAR(10), [jb].[JobID])
                     FROM   @Jobs [jb]
                     WHERE  [jb].[ItemID] = [i].[ItemID] --Change here as you're looking for JobID associated to the Item.
                     FOR XML PATH('') --No need to set TYPE or use '.value'
                 )
               , 1
               , 1
               , ''
                ) AS JobIDArray 
FROM       @Jobs [j]
INNER JOIN @Items [i]
    ON [i].[ItemID] = [j].[ItemID]
GROUP BY   [i].[ItemID]
         , [i].[Colour]
         , [i].[Size]
         , [j].[ExpressJob];

Use identity column in a string concat during an insert operation

No. It's not possible to reference the value assigned to the AUTO_INCREMENT column within the INSERT statement that adds the row. The value is not available within the statement. It's also not available in a BEFORE INSERT trigger.

You might be tempted to create an AFTER INSERT trigger, to assign a value to the fooId column, based on the value assigned to the fooSn AUTO_INCREMENT column. But that would require an UPDATE of the row that was just inserted, and you will find that if you attempt to do that within an AFTER INSERT trigger, MySQL will raise error 1442.

how to add an ID with both string and auto incremented number in sql

This exactly my case was, and I have solved, see my answer there in similar question.

Let me try to answer it systematically as well.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
type varchar)ENGINE = InnoDB;

Then create data values like,

insert into my_seq(min_value,max_value,last_value,increment_by,type) 
  values(1,99999999,1,1,'foo#','DS'),(1,999999999,1,1,'foo#','MS'),(1,999999999,1,1,'foo#','DOC');

Make sure you have auto-commit=false.
Then, do it like this in your app, or database code,

//very import to begin the transaction
begin;
select CONCAT(type_val,'-',last_value) from my_seq where type_val=? FOR UPDATE;

Read the result in App or database procedure/trigger

update my_seq set last_number=last_number+1 where type_val=?;
commit;

Make sure there is index on type_val.

I believe this should work.

---

Related Topics