mysql - How to insert ignore MySQL rows into another table and update a field when duplicates found?

Question

I'm trying to make a telemarketing campaign management web application. Application user can upload Excel sheets including telephone number + status fields to the application and update the "status" when they are making a call to clients. When an Excel sheet is uploaded, all the data goes to a table called "tp_numb". Then I created "MySQL insert ignore" to copy data from "tp_numb" to a table called "ok_list". I need to update the "status" field in the "ok_list" table when duplicates are found. If duplicates are not found, the data should be inserted into the "ok_list" table.

CREATE TABLE `tp_numb` (
  `id` int(11) NOT NULL,
  `number` varchar(10) NOT NULL,
  `ref_id` int(10) NOT NULL,
  `status` int(1) NOT NULL DEFAULT '0',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `ok_list` (
  `id` int(11) NOT NULL,
  `tp_numb_id` int(10) NOT NULL,
  `number` varchar(10) NOT NULL,
  `status` int(2) NOT NULL,
  `notes` text,
  `status_updated_on` datetime DEFAULT NULL,
  `ref_id` int(10) NOT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `assign` int(10) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT IGNORE INTO ok_list (tp_numb_id, number, status, ref_id)
SELECT t.id, t.number, t.status, r.ref_id
FROM tp_numb t, reference r
WHERE t.ref_id = r.ref_id;

Above code is the code I have been trying to improve with a function to update the "status" field in the "ok_list" table. Because the status of telephone numbers should be the status according to the last uploaded Excel file. How can I update the status when a duplicate found? How can I insert a record when duplicates are not found?

标签： mysqlsqlinsert-update

Answer 1

I think you want on duplicate key update:

INSERT INTO ok_list (tp_numb_id, number, status, ref_id)
    SELECT t.id, t.number, t.status, r.ref_id
    FROM tp_numb t JOIN
         reference r
         ON t.ref_id = r.ref_id
    ON DUPLICATE KEY UPDATE status = VALUES(status);

Your question supposed that there is only one row for each number. I'm not actually sure which column that is, but you should have a unique constraint/index so it know which row gets updated with the status. I think it is tp_numb_id, which would be;

create unique index unq_ok_list_tp_numb_id on ok_list(tp_numb_id);