CODEWITHSUNDEEP
mysqlinsert
Smudger
Smudger

Reputation: 10771

MySQL insert statement - on duplicate key update

I have a MySQL insert statement that inserts data from an existing Despgoods table into a new table DespGoods_AllDetails. The primary key in both tables is 'case no' and the autoincrement column in despgoods_alldetails.

Each case number has a load number that changes from time to time. How can I adjust my statement so that when a duplicate key is detected, it simply updates the load number of that record rather than just fail/ ignore/ skip the record to be inserted. some records will not be duplicated and some will. The non duplicated records still need to be inserted whilst the duplicate records just need to be updated.

Do I use replace into or ON DUPLICATE KEY UPDATE? How do I use this syntax in my existing query?

insert ignore into despgoods_alldetails 
(`loc or exp` ,`LOC TRNSPRT DEST` ,`load number` ,`case no` ,`lot id` ,`metal mass` ,`gross mass` ,`CASE CREATION DATETIME` ,`CASE PROGERESS STATUS` ,`PROGRESS STAT DATETIME` ,`case width` ,`case height` ,`case length` ,`storage` ,`del prty` ,`CRTD DEPT` ,`CASE STATUS` ,`lab released` ,`PROD STREAM` ,`PROD SUB STREAM` ,`CSC Name` ,`Customer` ,`DAYS STANDING` ,`EXP SHIPREL` ,`LOC WHS EREL` ,`LOC CUST REL` ,`REC CHANGED AT` ,`transporttypename` ,`transporttypeid` ,`customerID`,`LOCStatus`)
SELECT `despgoods`.`loc or exp` ,`despgoods`.`LOC TRNSPRT DEST` ,`despgoods`.`load number` ,`despgoods`.`case no` ,`despgoods`.`lot id` ,`despgoods`.`metal mass` ,`despgoods`.`gross mass` ,`despgoods`.`CASE CREATION DATETIME` ,`despgoods`.`CASE PROGERESS STATUS` ,`despgoods`.`PROGRESS STAT DATETIME` ,`despgoods`.`case width` ,`despgoods`.`case height` ,`despgoods`.`case length` ,`despgoods`.`storage` ,`despgoods`.`del prty` ,`despgoods`.`CRTD DEPT` ,`despgoods`.`CASE STATUS` ,`despgoods`.`lab released` ,`despgoods`.`PROD STREAM` ,`despgoods`.`PROD SUB STREAM` ,`despgoods`.`CSC Name` ,`despgoods`.`Customer` ,`despgoods`.`DAYS STANDING` ,`despgoods`.`EXP SHIPREL` ,`despgoods`.`LOC WHS EREL` ,`despgoods`.`LOC CUST REL` ,`despgoods`.`REC CHANGED AT` ,`customers`.`transporttypename` ,`customers`.`transporttypeid` ,`customers`.`customerID` ,'loadplanned'
FROM despgoods
INNER JOIN customers
ON 
despgoods.customer = customers.customername
 ";

Upvotes: 0

Views: 1498

Answers (1)

Jan S
Jan S

Reputation: 1837

You can use ON DUPLICATE KEY UPDATE for example :

...
FROM despgoods
INNER JOIN customers
ON 
despgoods.customer = customers.customername
ON DUPLICATE KEY UPDATE `load number` = VALUES(`load number`);

you can also use REPLACE INTO. The only thing is that it will delete the entire row and then insert.

Upvotes: 2

Related Questions