Bulk Update based on field in query field condition

Hi everyone. I have alot of data (50 M) to update based on some conditions and eventually a Task Scheduler will do the same job every 1 hour for the new incoming records so I preferably don't want to use a for loop because this will create a query for every record

I've read that for bulk update, the suggested method is using the Query Builder so i've managed to do this line of code

$clientVehiculeTable = (new Vehicule())->getTable();

DB::table($clientVehiculeTable)
		->whereDate("time", ">", Carbon::now()->subMinutes(60))->update(array("is_valid_vin" => `Insert condition here`));

The thing is, I want to update is_valid_vin based on some SQL request of field vin_number in the same table

It's a basic Vin Validation that I did and it would ressemble of something like this

	->where("vin_number", "!=", "00000000000000000")
			->whereRaw('LENGTH(vin_number) = 17')
			->whereRaw("SUBSTR(vin_number, 9, 1) in (0,1,2,3,4,5,6,7,8,9,'X')")
			->whereRaw("SUBSTR(vin_number, length(vin_number) -4) REGEXP '^[0-9]+'");

Is there a way to combine this in a bulk update ? Return true or false for all elements of the query ? Something in the line of

DB::table($clientVehiculeTable)
		->whereDate("time", ">", Carbon::now()->subMinutes(60))->update(array("is_valid_vin" => ("vin_number", "!=", "00000000000000000")
			->whereRaw('LENGTH(vin_number) = 17')
			->whereRaw("SUBSTR(vin_number, 9, 1) in (0,1,2,3,4,5,6,7,8,9,'X')")
			->whereRaw("SUBSTR(vin_number, length(vin_number) -4) REGEXP '^[0-9]+'"); ));

Thank you very much for your time.

Alexb0903

5 years ago

Best Answer

Level 3

Found the solution, It was to insert DB::RAW( CASE WHEN ) in the ->update([]) method like this

->DB::table($vehiculeTable)
			->where("time", ">=", Carbon::now("America/Toronto")->subMinutes(1))
			->update([
			'is_active' =>  DB::raw(
'CASE WHEN (vin_number != "00000000000000000" AND length(vin_number ) = 17) 
			 THEN 1
			 ELSE 0
			 END'
			)
		]);

mabdullahsari

5 years ago

Level 16

Posted something but seems it does not matter anymore, so removed.

Please or to participate in this conversation.