Updating the data of an existing column and copying data with migrations

Question

Is it possible to add a new column, copy some data to this new column and update the data in another column with a Laravel migration?

I have a table something like this;

id	item	price
1	ItemOne	100.00
2	ItemTwo	200.00

Now what I need to do is,

1. add a new column, old_price to this table
2. copy the values in the price column to newly added old_price column
3. multiply the values in the price column with 5 and update the same column

The new table should look something like this;

id	item	price	old_price
1	ItemOne	500.00	100.00
2	ItemTwo	1000.00	200.00

Is it possible to to achieve this with a migration or a seed or something?

This change needs to be done on an application that is already in production so dropping and re-creating the tables is not an option for me here.

Also the old_price column is created just to keep a reference of the current values of the price column. It will not be updated after this and probably will be removed in an later update if everything is going right with the new prices. So I don't need to use any model events to update the column afterwards.

Any help you can give me on this is really appreciated. Thanks.

Answer 1

Create a new migration.

Version 1. create automatically by command:

php artisan make:migration add_price_old_to_products_table

Version 2. create manually something like this:

2021_08_18_163618_add_price_old_to_products_table.php

Manage the content by following the 3 steps in the code:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class AddPriceOldToProductsTable extends Migration
{
    public function up()
    {
        // 1. creating a new column
        Schema::table('products', function (Blueprint $table) {
            // this is just an example, you can change this
            // NOTE: make sure that the type is the same as "price" column
            // for avoiding type conflicts
            $table->decimal('price_old', 10, 2)->nullable();
        });

        // 2. copying the existing column values into new one
        DB::statement("UPDATE products SET price_old = price");

        // 3. update the old/existing column
        // CHANGE YOUR "price" COLUMN HERE...
    }

    public function down()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropColumn('price_old');
        });
    }
}

Run that migration for creating a new column:

php artisan migrate