Update multiple records in db that are not yet in the table

Question

I get an array() schools in which the person participated. For the user [e-mail] array has 3 degree , which it earned. My table has only 2 of its 3 degrees.

What is the easiest way to insert Sarah degrees that are not already in the table?

Start table:

 email school degree [e-mail] Yale BA [e-mail] Emery PHD [e-mail] ClownU BS [e-mail] Harvard BA [e-mail] Harvard Masters 

Final table:

 email school degree [e-mail] Yale BA [e-mail] Emery PHD [e-mail] ClownU BS [e-mail] Harvard BA [e-mail] Harvard Masters [e-mail] Harvard PHD 

Parameters that I reviewed:

• Delete all entries Sarah. Insert all of Sarah's notes.
• For each entry, see if it exists. If so, do nothing, otherwise write a record.
• Some magic CI function that updates_batch or inserts_batch if a particular record does not exist?

Answer 1

 Insert Into [Start-Table] (email, school, degree) ( select b.email, b.school, b.degree from [End-Table] b where NOT EXISTS (Select email, school, degree from [Start-Table] where email = b.email and school = b.school and degree = b.degree) ) 

Answer 2

I had the same problem as you, and I solved it like this: 1-Delete all the entries of the user you want, and don’t insert the package like a code hit.

  $employer_office = $this->input->post('employer_office'); $emp_job_type = $this->input->post('job_type'); $job_title = $this->input->post('job_title'); $job_appointment_date = $this->input->post('job_appointment_date'); $job_duration = $this->input->post('job_duration'); $job_place = $this->input->post('job_place'); $type_of_relation = $this->input->post('type_of_relation'); $monthly_salary = $this->input->post('monthly_salary'); $remarks = $this->input->post('remarks'); $all_array = array(); if($employer_office) { if(is_array($employer_office)) { for($j=0; $j<count($employer_office); $j++) { $form_arr = array( 'employer_office' =>$employer_office[$j], 'job_type' =>$emp_job_type[$j], 'job_title' =>$job_title[$j], 'job_appointment_date' =>change_datei($job_appointment_date[$j]), 'job_duration' =>$job_duration[$j], 'job_place' =>$job_place[$j], 'type_of_relation' =>$type_of_relation[$j], 'monthly_salary' =>$monthly_salary[$j], 'remarks' =>$remarks[$j], 'regdate' =>date('Ymd H:m:s'), 'userid' =>$id ); array_push($all_array, $form_arr); } } } if($this->history_model->delete_all('ast_jobs_history', $id)==TRUE) { if($this->history_model->all_insert('ast_jobs_history', $all_array)==TRUE) { $this->session->set_flashdata("msg","<span class='m_success'>".$this->lang->line('global_update_success')."</span>"); redirect('history/home/list_dy','refresh'); } else { $this->session->set_flashdata("msg","<span class='m_error'>".$this->lang->line('global_update_error')."</span>"); redirect('history/home/list_dy','refresh'); } } else { $this->load->view('unauthorized'); } 

and inside the insert_batch it model.

  function all_insert($tbl, $data=array()) { if(is_array($data)) { $this->db->trans_start(); $this->db->insert_batch($tbl,$data); $this->db->trans_complete(); return TRUE; } else { return FALSE; } } 

make some changes, and I hope this gives you a hint on how to solve your problem.

or you can also make a request if you do not want to delete records of a specific identifier, get data according to the identifier and use the array_search () or in_array () function to filter your published data and get the record that you want to insert, and then paste it into the database data.

Answer 3

If you keep the same identifier for your records, it doesn’t matter, just delete and re-insert all the data. If you want to keep the same identifiers for old records, you can also delete them, but first ask the database to get the identifiers and create an array with them. Then you can insert all the new data.

But if I were you, I would create a function that scans the database, looking for existing records. If a record exists, it updates it. If not, he creates a new one. I believe this is the cleanest way to solve this problem.