Update join 2 table using import excel in laravel

Question

i want to update 2 relation table, i have tried it but i got an error. i am using laravel 5.7 and Maatwebsite 3.1.

This is my controller, i put excel data in $data:

public function import(){
 $data = Excel::toArray(new AssignmentFAImport, request()->file('file')); 
    if ($data) {
        collect(head($data))
        ->each(function ($row, $key) {
            DB::table('fa_transaction')
                ->join('customer', 'fa_transaction.fa_transaction_id', '=', 'customer.fa_transaction_id')
                ->where('fa_transaction_id', $row['fa_transaction_id'])
                ->update(array_except($row, ['fa_transaction_id']));
        });

        return "Success";
    }else{
        return "Can not update";
    }
}

and this import class :

<?php

namespace App\Imports;

use App\Models\Transaction\FA_transaction;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class AssignmentFAImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
    return new FA_transaction([
        'nomen' => $row['nomen'],
        'dispatch_group' => $row['dispatch_group'],
        'address' => $row['address'],
        'fa_transaction_id' => $row['fa_transaction_id'],
        'fa_type_cd' => $row['fa_type_cd'],
        'pcezbk' => $row['pcezbk'],
        'status' => $row['status'],
        'created_at' => $row['created_at'],
        'worker_id' => $row['worker_id'],
        'assign_date' => $row['assign_status'],
        'reassigned_date' => $row['reassigned_date'],
        'assign_status' => $row['assign_status'],
        'urgent_status' => $row['urgent_status'],
        'priority_status' => $row['priority_status'],
        'sending_status' => $row['sending_status']
    ]);
   }
  }

and i got an error like this:

Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Ambiguous column name 'fa_transaction_id'. (SQL: update [fa_transaction] set [nomen] = 60215072, [dispatch_group] = CC31-DR , [address] = JL KALI BARU TMR NO 32 RT 012 RW 013 , [fa_type_cd] = FA-T4E , [pcezbk] = 0760309, [status] = P , [created_at] = 2018-11-16 00:00:00.000, [worker_id] = 2, [assign_date] = 2019-01-22 08:54:48.000, [reassigned_date] = , [assign_status] = Assigned, [urgent_status] = 1, [priority_status] = , [sending_status] = from [fa_transaction] inner join [customer] on [fa_transaction].[fa_transaction_id] = [customer].[fa_transaction_id] where [fa_transaction_id] = 0210559377)

i don't know why column name is Ambiguous, how to fix it?

Is there any reason why you are doing the join for an update statement? — Mozammil, Jan 23 '19 at 10:29
because the excel data is not get in one table, but it get from 2 relation table, so when upload running, it must update 2 table, not one table only. i use 'fa_transaction_id' for join the table. — Rahmat Effendi, Jan 23 '19 at 10:37

Update join 2 table using import excel in laravel

0 Answers0