0

I am inserting a new row into my table. Is it possible to use the primary key (id) as part of the value of a column in this same row during the insert? For example, say I just inserted a new row with id of 6, is it possible to have the image_address column in that same row be something like "folder/6" with the 6 coming from the id? This is on an INSERT.

TABLE
id (primary key, auto increment)
image_address (I want this to include the id in it)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3451821
  • 123
  • 7
  • 2
    There is no point in doing that, you can concatenate the 2 values when you query the data. – Shadow Sep 12 '17 at 20:18

2 Answers2

1

You can place a trigger after INSERT which updates image_address and uses the new id. But really why do you want to do this, when you can programmatically recreate that 'folder/6' path whenever you want? You can do it in your scripts or in your select. Ex:

SELECT id, CONCAT('folder/',id,'/') as path FROM table
Vts
  • 41
  • 7
1

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.

ScottieB
  • 3,958
  • 6
  • 42
  • 60