dlofrodloh February 2016

Copying rows whilst ignoring duplicates based on specific column

I want to copy rows from my table call_list_row whilst changing the column listid, but I only want to copy the row and change the listid if no other row with the $new_list_id already has a row with that particular candid.

This is the SQL I have to copy the rows and change the listid:

$sql= "INSERT INTO call_list_row (listid,candid,date,byuser,status,emailed)
         SELECT '".$new_list_id."',candid,date,byuser,status,emailed 
         FROM call_list_row WHERE listid='".$list."'";

How can I modify this so that there will be no rows in the table that have the new $new_list_id with the same candid?

Answers


Barmar February 2016

Create a unique index on the composite key listid, candid:

CREATE UNIQUE INDEX x_listid_candid ON call_list_row(listid, candid);

Then you can use INSERT IGNORE, so that if there's a row with that combination already in the table, the INSERT will be skipped with no error.

$sql= "INSERT IGNORE INTO call_list_row (listid,candid,date,byuser,status,emailed)
     SELECT '".$new_list_id."',candid,date,byuser,status,emailed 
     FROM call_list_row WHERE listid='".$list."'";

If you can't create this index (maybe duplicates are allowed, but you don't want to create any new duplicates in this procedure), you can check in the query.

$sql= "INSERT IGNORE INTO call_list_row (listid,candid,date,byuser,status,emailed)
     SELECT '".$new_list_id."',candid,date,byuser,status,emailed 
     FROM call_list_row AS c1 WHERE listid='".$list."'
     AND NOT EXISTS (
        SELECT * FROM call_list_row AS c2
        WHERE listid = '$new_list_id' AND c1.candid = c2.candid)";

Post Status

Asked in February 2016
Viewed 1,575 times
Voted 12
Answered 1 times

Search




Leave an answer