0

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

V-T
  • 756
  • 2
  • 9
  • 22

2 Answers2

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.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Aidan
  • 11
  • 3
  • If I've completely missed the mark on what you're trying to ask then maybe providing some sample data will help. – Aidan Aug 24 '16 at 12:01
  • Still, from the link shared, didn't get any solution for MySQL. How multiple rows can update with different where condition ids? – V-T Aug 24 '16 at 13:04
  • Is this really an answer? – Jitesh Sojitra Aug 24 '16 at 13:10
  • @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. – Robert Harvey Aug 24 '16 at 14:59
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

mrid
  • 5,782
  • 5
  • 28
  • 71