2

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?

Poka Yoke
  • 373
  • 3
  • 8
  • 27
  • 3
    Possible duplicate of [How to get ID of the last updated row in MySQL?](http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql) – Machavity Nov 28 '15 at 18:29
  • @Machavity I'm not sure if there is a way in SQL that can bind the image_path column name with another column. So is there such a solution? – Poka Yoke Nov 28 '15 at 18:37
  • My opinion is that it's easier to make it the "long" way, but try this question on SO: http://stackoverflow.com/questions/17266442/in-mysql-how-can-i-use-the-autoincrement-value-in-another-column-at-the-time-of – ulentini Nov 28 '15 at 18:57

2 Answers2

2

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;
Sam Ivichuk
  • 999
  • 1
  • 10
  • 22
-1

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;
Machavity
  • 30,841
  • 27
  • 92
  • 100
Preethi Raju
  • 136
  • 5