# Mass Updates MySQL

**URL:** <https://forum.inductiveautomation.com/t/mass-updates-mysql/51088>\
**Category:** Ignition\
**Tags:** ignition81, database, vision\
**Created:** [September 17, 2021, 11:26pm UTC](https://forum.inductiveautomation.com/t/mass-updates-mysql/51088 "2021-09-17T23:26:11Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [September 17, 2021, 11:26pm UTC](https://forum.inductiveautomation.com/t/mass-updates-mysql/51088/1 "2021-09-17T23:26:11Z")

</div>

Is there a fancy way to do mass updates, like mass inserts?

> [@Mass Inserts MySQL](https://forum.inductiveautomation.com/t/mass-inserts-mysql/9646):
>
> Hello, Well I have to insert a 1000 to 10000 rows array (dtaMSSQLAllPieces) with a wooping 7 columns into my MySQL database (ProdRestigouche) I’m testing the runPrepUdpate with a Transaction Token and the behavior seems to be the same as the ruPrepUpdate by itself… Here’s what I’ve got so far… The MessageBox is just as a process time identifier, and I’ve notice that the data is written into my database before the commit MessageBox. To what I understand, the data shouldn’t be written to the …

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 18, 2021, 12:00am UTC](https://forum.inductiveautomation.com/t/mass-updates-mysql/51088/2 "2021-09-18T00:00:06Z")

</div>

Probably the easiest to implement.

[https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [September 18, 2021, 12:02am UTC](https://forum.inductiveautomation.com/t/mass-updates-mysql/51088/3 "2021-09-18T00:02:25Z")

</div>

Thanks, didn’t notice that. Will give it a try.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 18, 2021, 1:51am UTC](https://forum.inductiveautomation.com/t/mass-updates-mysql/51088/4 "2021-09-18T01:51:24Z")

</div>

If you use [PostgreSQL](https://www.postgresql.org/docs/13/sql-update.html), you can use a FROM clause subquery composed of a bunch of unioned constant SELECTs. Something like this:

```auto
Update myTable Set col_a = subq.a, col_b = subq.b
From (Select ? as a, ? as b, ? as c
	Union All Select ?, ?, ?
	Union All Select ?, ?, ?
	Union All Select ?, ?, ?
	Union All Select ?, ?, ?
	Union All Select ?, ?, ?) subq
Where myTable.col_c = subq.c

```

You might need casts after the question marks on the first Select to set the column types.

MariaDB appears to allow a sub-queries in its multi-table form of Update, but I haven’t worked with that.