Mysql REPLACE INTO query for Multiple rows insertion

Question

I'm trying to add multiple rows using 'replace into' , but its updating one row.

REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)  
VALUES  (NEW.id,1,'USD',0,1,0,0,NEW.ip_address),
(NEW.id,1,'GBP',0,1,0,0,NEW.ip_address),
(NEW.id,1,'EUR',0,1,0,0,NEW.ip_address),
(NEW.id,1,'BTC',0,1,0,0,NEW.ip_address);

Is this right, the way am doing it?

PS: am employing a trigger to update the balance table whenever an user is created in the users table. It works well with if I replace one row, for multiple rows, it updates only the last values.

Community · Accepted Answer · 2017-04-13 12:42:44Z

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ?

REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?)

Here are two comments that shows the LIFO approach to processing triggers

Posted by Atif Ghaffar on September 23 2007 9:12pm [Delete] [Edit] PLEASE Note that the REPLACE does a DELETE operation.

We did not realize this and had the triggers that should be triggered on DELETE triggered.

After checking all the code, we just found a script that does a replace to refresh the values of some fields.

We should have had used "insert into ... on duplicate update" syntax instead.

Posted by J Mike on May 4 2009 11:06pm [Delete] [Edit] If you are using REPLACE INTO... triggers are fired in this order (if delete of duplcate key is used):

before insert

before delete

after delete

after insert

Your problem ? An UPDATE trigger will never fire off if this has to be done multiple times in a single operation. At least, I don't see that happening.

SUGGESTIONS

You may want to use INSERT ... ON DUPLICATE KEY UPDATE (See my answer to Update one table from another table while sorting that table based on one column)
Stick to doing REPLACE on one row at a time

Stack Exchange Network

Mysql REPLACE INTO query for Multiple rows insertion

1 Answer 1

SUGGESTIONS

Your Answer

Linked

Hot Network Questions

Mysql REPLACE INTO query for Multiple rows insertion

1 Answer 1

SUGGESTIONS

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions