Update multiple table in Bulk using MySQL

Question

Is there any way to update multiple table in bulk. I found solution for bulk update using single as well as update multiple table using single query. But then looking for a combined solution.

eg: Currently doing like this.

UPDATE a, b, c 
SET a.address = "$address", b.address = "$address", c.address = "$address" 
WHERE a.id = b.a_id AND a.id = c.a_id AND a.id = 123

UPDATE a, b, c 
SET a.address = "$address", b.address = "$address", c.address = "$address" 
WHERE a.id = b.a_id AND a.id = c.a_id AND a.id = 234

etc

This is my current script that update every address one by one.

To update multiple entries in single query I can use like,

UPDATE a SET address = CASE
    WHEN id = 123 THEN 'address1'
    WHEN id = 234 THEN 'address2'
    END

Is there any way to combine these queries, to update multiple table as well as multiple rows in single query?

Thanks

above link tells how to update multiple table only. But I need bulk update. Not one by one. — Mark
– Mark, Commented Aug 24, 2016 at 11:43

Robert Harvey · Accepted Answer · 2016-08-24 14:58:13Z

1

I'm not sure I follow what you mean by "bulk". I assume you mean update multiple entries in each table. In that case you'd need a condition that returns multiple entries to be updated. Assuming that id is your PK it'll only return one entry.

Your WHERE clause looks very similar to what you'd use on a JOIN. Maybe you should try JOIN your tables and then update them. Check this out:

https://stackoverflow.com/a/1293347/4024150

UPDATE a JOIN b ON a.id = b.id
SET a.address = '$address', b.address = '$address
WHERE (some clause that will return all the entries you want)

I've simplified it to two tables for demonstration but basically your JOIN will present you with one big table joined on the id field. You can then use the WHERE clause to identify the fields you want to update over the entire joined table.

You said above that you found a solution to bulk update a single table. This solution combined with the JOIN should yield the desired results.

edited Aug 24, 2016 at 14:58

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

answered Aug 24, 2016 at 12:00

Aidan

113 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aidan Over a year ago

If I've completely missed the mark on what you're trying to ask then maybe providing some sample data will help.

Mark Over a year ago

Still, from the link shared, didn't get any solution for MySQL. How multiple rows can update with different where condition ids?

Jitesh Sojitra Over a year ago

Is this really an answer?

Robert Harvey Over a year ago

@Aidan: Do note that you're not in a chat window. This is a Question and Answer site. Questions are expected to look like questions, and answers are expected to look like answers.

mrid · Accepted Answer · 2016-08-24 16:14:22Z

0

You can achieve this using transaction

BEGIN TRANSACTION;

UPDATE Table1
SET Table1.field1 = 'new value1' 
WHERE condition1

UPDATE Table2
SET Table2.field3 = 'new value2'
WHERE condition2

COMMIT;

NOTE : all the queries you want to execute in bulk should be within BEGIN and COMMIT

answered Aug 24, 2016 at 16:14

mrid

5,7886 gold badges34 silver badges78 bronze badges

1 Comment

Mark Over a year ago

still multiple query. Am looking for, single query is possible or not.

Collectives™ on Stack Overflow

Update multiple table in Bulk using MySQL

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related