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?
Advertisement
Answer
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);