How to make a Bulk update with two primary keys in the stored mysql database?

Asked

Viewed 37 times

0

I need to perform an update several times as follows: 

UPDATE my_table set column1='value1', column2='value2' where primary_key1=1 and primary_key2=2;

UPDATE my_table set column1='value3', column2='value4' where primary_key1=3 and primary_key2=4;

UPDATE my_table set column1='value5', column2='value6' where primary_key1=5 and primary_key2=6;

.
.
.

UPDATE my_table set column1='valueX', column2='valueY' where primary_key1=W and primary_key2=Z;

How do I perform this process in Bulk in the stored database of a mysql database? Is it possible?

mysql stored-procedures

  • Would you like to do this process in a loop using for example a foreach? Pass values to be changed by parameter or get through a select?

  • I want to run this query only once and perform multiple updates. If the table has only one primary key, it is very quiet. My question is when there is composite primary key.

1 answer

1

I found a solution, would use INSERT with ON DUPLICATE KEY UPDATE. It will attempt to perform an Insert and, if it is a duplicate record, it updates the last record.

Example: 

INSERT INTO my_table (column_1, column_2, column_3, column_4)
VALUES
(1,1,'val','val'),
(1,2,'val','val'),
(1,3,'val','val'),
(2,1,'val','val'),
(2,2,'val','val'),
(2,3,'val','val')
ON DUPLICATE KEY
        UPDATE column_3=values(column_3), column_4=values(column_4);

With column 1 and 2 as Keys.

  • Now I need to figure out how to do this in a stored trial.

Browser other questions tagged mysql stored-procedures

You are not signed in. Login or sign up in order to post.