Mass Updates MySQL

[jlandwerlen]

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

Mass Inserts MySQL

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 …

[JordanCClark]

Probably the easiest to implement.

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

[jlandwerlen]

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

[pturmel]

If you use PostgreSQL, you can use a FROM clause subquery composed of a bunch of unioned constant SELECTs. Something like this:

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.