UPDATE AND INSERT multiple rows into MySQL database

Question

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.

I am looking for a where condition in that query so I can update price and time for those which have same from and destination value — Mac Taylor
– Mac Taylor, Commented Nov 22, 2015 at 4:48

Farshad · Accepted Answer · 2015-11-22 05:37:57Z

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

Ramin Darvishov · Accepted Answer · 2015-11-22 05:04:19Z

0

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

edited Nov 22, 2015 at 5:04

answered Nov 22, 2015 at 4:49

Ramin Darvishov

1,0392 gold badges15 silver badges30 bronze badges

2 Comments

Mac Taylor Over a year ago

I want to have insert and update command on one query , So if there is no row with same from and destination value , then Insert otherwise update the price of that row

Ramin Darvishov Over a year ago

generate two command. First for update - above coomand, second for insert: INSERT INTO me_cities( from, destination, price, updated) VALUES WHERE NOT EXIST SELECT WHERE from='ERR' AND destination='MRHD` AND price='91,000' AND updated`=now()

Collectives™ on Stack Overflow

UPDATE AND INSERT multiple rows into MySQL database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related