Mass updating table to set value of column to value of another column

What is the proper way to achieve this SQL in a database agnostic way using Eloquent?

UPDATE my_table
SET my_table.b = my_table.a
WHERE my_table.b IS NULL

This, unfortunately, is not working.

Post::withTrashed()
    ->whereNull('bumped_last')
    ->update([
        "posts.bumped_last" => "posts.reply_last",
    ]);

The applied value is 0, which I assume is because the update code is interpreting the value "posts.reply_last" literally and typecasts it to 0 when converting to int.

Any ideas?

Jawsh

10 years ago

Level 2

Okay, I actually got this one figured out.

This was a part of a migration process. The finalized code looks like this. The update code knows to handle the output of DB::raw() as a literal value, and that's great because it lets you use the rest of the Eloquent system except for that point.

Though, you can't use Post::raw() for some reason, so you do have to manually import the DB facade.

use App\Post;

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

use Illuminate\Support\Facades\DB as DB;

class AddPostBumpedLast extends Migration {
    
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('posts', function(Blueprint $table)
        {
            $table->timestamp('bumped_last')->nullable()->default(null)->after('reply_last');
        });
        
        Post::withTrashed()
            ->whereNull('bumped_last')
            ->update([
                "bumped_last" => DB::raw("`reply_last`"),
            ]);
    }
    
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('posts', function(Blueprint $table)
        {
            $table->dropColumn('bumped_last');
        });
    }
    
}

I'll move this to code review.

15 likes

Please or to participate in this conversation.