CODEWITHSUNDEEP
phpmysql
Mac Taylor
Mac Taylor

Reputation: 5148

UPDATE AND INSERT multiple rows into MySQL database

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.

Upvotes: 3

Views: 72

Answers (2)

Farshad
Farshad

Reputation: 1015

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() ;

Upvotes: 1

Ramin Darvishov
Ramin Darvishov

Reputation: 1039

Generate and execute this commands

UPDATE `me_cities` SET `price`='91,000', `updated`=now() WHERE EXIST (SELECT WHERE `from`='ERR'  AND `destination`='MRHD` AND `price`='91,000' AND `updated`=now())

Upvotes: 0

Related Questions