CODEWITHSUNDEEP
phpmysqlsqldatabase
Poka Yoke
Poka Yoke

Reputation: 403

Use inserted id for another column value

I have an identity column (id) that auto-increments. 

id|name|image_path

I want to know if there is some way using mysql, to use the newly inserted id in the image_path. For example if a new row is inserted and got the id 2 I want the image_path to be "/images/2.png". Or do I have to use the traditional way, by inserting and then fetching this ID then updating the entry?

Upvotes: 2

Views: 1008

Answers (2)

Preethi Raju
Preethi Raju

Reputation: 136

Yes, it is possible with oracle. We have dynamic sql feature.

have tried the below.

Created a sequence and then created a procedure which takes id as input and creates an insert statement dynamically which will fulfill your requirement. 

create sequence seq1 start with 1;

create table image1(id1 number,image varchar2(50));

create or replace procedure image1_insert(id1 in number)
as
sql_stmt varchar2(50);
image_path varchar2(50);
begin
sql_stmt:='insert into image1 values(:1,:2)';
image_path:='/image/'||id1||'.png';
execute immediate sql_stmt using id1,image_path;
end;

begin
image1_insert(seq1.nextval);
end;


id  image
4   /image/4.png
5   /image/5.png

select *from image1;

Upvotes: -1

Sam Ivichuk
Sam Ivichuk

Reputation: 1028

My opinion is that it is impossible to do with one query. You won't know new autoincrement value until row will be inserted. Still you can write 1 query to achieve what you want (actually 2 queries would be executed):

    insert into `t`(`id`, `name`, `image_path`)
    values(
      (SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
       WHERE `table_name` = 't'),
      '1234',
      concat(
        '/images/',
        (SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
         WHERE `table_name` = 't'),
        '.png'
      )
    )

Anyway much safer would be:

START TRANSACTION;
set @c = (select ifnull(max(`id`),0) + 1 from `t`);
insert into `t`(`id`, `name`, `image_path`) values (@c,'123',concat('/images/',@c,'.png'));
COMMIT;

Upvotes: 2

Related Questions