I wrote this code to insert multiple rows into database, and I need to update duplicates .
INSERT INTO `me_cities`( `from`, `destination`, `price`, `updated`)
VALUES
('ERR','MRHD','91,000',now()),
('ERR','KIH','106,800',now()),
('ERR','SYZ','86,000',now()),
('ERR','AWZ','121,000',now()),
('ERR','IFN','116,741',now()),
('ERR','GSM','111,000',now()),
('ERR','NJF','141,000',now()),
('ERR','IST','351,000',now()),
('ERR','BND','116,000',now()),
('ERR','DEF','120,200',now()),
('ERR','','151,000',now()),
('ERR','BUZ','161,000',now())
ON DUPLICATE KEY UPDATE `price`=VALUES(price),`updated`=VALUES(now());
But I have error on this query. I need to update the row if only from and destination field are as same as next row.
If I got your topic , I think you should do something like this :
INSERT INTO `me_cities`( `from`, `destination`, `price`, `updated`)
VALUES
('ERR','MRHD','91,000',now()),
('ERR','KIH','106,800',now()),
('ERR','SYZ','86,000',now()),
('ERR','AWZ','121,000',now()),
('ERR','IFN','116,741',now()),
('ERR','GSM','111,000',now()),
('ERR','NJF','141,000',now()),
('ERR','IST','351,000',now()),
('ERR','BND','116,000',now()),
('ERR','DEF','120,200',now()),
('ERR','','151,000',now()),
('ERR','BUZ','161,000',now())
ON DUPLICATE KEY UPDATE `price`=VALUES(`price`),`updated`=VALUES(`updated`);
UPDATE , if you want to update every each of them, first make the from and destination columns unique and relevant unique keys , something like this query
INSERT INTO `me_cities`( `from`, `destination`, `price`, `updated`)
VALUES
('ERR','MRHD','91,000',now()) ON DUPLICATE KEY UPDATE `price`='' ,`updated`=now() ;
INSERT INTO `me_cities`( `from`, `destination`, `price`, `updated`)
VALUES
('ERR','MRHD','91,000',now())ON DUPLICATE KEY UPDATE `price`='' ,`updated`=now() ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With