How to update multiple rows sharing same foreign key in Codeigniter-CodePudding

I have 2 tables; db_file_acess and db_file_access_details. I have a column subjects_id in db_file_access_details table. From the UI, I can select several subjects[array] with other details and insert them to db_file_acess table, and subject_ids to the db_file_access_details. Insertion is OK but when I update the two tables, I can't seem to update all the rows. This is my update query

$result = array();
        foreach ($subject_id as $key => $val) {
            $result[] = array(
                'file_access_id' =>$q_id,
                'subjects_id' => $_POST['subject_id'][$key]
            );
        }
        $this->db->update_batch('db_file_access_details', $result, 'file_access_id');

The query runs but it updates with only one id as seen in the screenshot below:

the result() array is as shared below:

Array
(
    [0] => Array
        (
            [file_access_id] => 45
            [subjects_id] => 1
        )

    [1] => Array
        (
            [file_access_id] => 45
            [subjects_id] => 3
        )

    [2] => Array
        (
            [file_access_id] => 45
            [subjects_id] => 4
        )

)

after update_batch query, the resultant results is as shown below What am I doing wrong?

CodePudding user response：

According to the docs update_batch('db_file_access_details', $result, 'file_access_id') produces:

UPDATE `db_file_access_details` SET `subjects_id` = CASE
WHEN `file_access_id` = 45 THEN 1
WHEN `file_access_id` = 45 THEN 2
WHEN `file_access_id` = 45 THEN 3
ELSE `subjects_id` END
WHERE `file_access_id` IN (45, 45, 45)

For all three rows, the first case matches, so all subject_ids are set to 1.

For update_batch to work correctly you need to use the id column.

Depending on what you want to achieve, you could also consider deleting all rows for the file_access_id in db_file_access_details and then inserting the $result array with insert_batch instead.