Although we have spoken several times of this framework MariaDB MySQL-today a new feature, I bring very useful when you need to perform repetitive tasks based on consultations. It is used foreach call. Here I present a practical example of how you’ve used to perform a specific task: to replace all null to zero in a table with a lot of fields values.
Locating the table fields
The best way of locating the fields that will be used in our task is by using the information_schema, an internal database needed forMySQL.
Knowing that the database is called inst7 and the table datos, the query to be used is this one:
SELECT c.column_name FROM information_schema.`COLUMNS` c
WHERE TABLE_SCHEMA = “inst7” and TABLE_NAME = “datos” ORDER BY c.column_name
And this is the outcome of the sentence:
common_schema foreach
Now we have all the fields ready to be modified from nulls values to zero. The SQL sentence will be something like this:
UPDATE datos SET campo = 0 WHERE campo is null
Using foreach
The documentation for foreach can be found here: http://code.openark.org/blog/mysql/mysql-foreach, so, let’s go to action.
Foreach has a really cool feature: the sintax is really easy to use, because we can use {1} and {2} for using the data of the first field and the second field. In our case, we only need the first one only.
The SQL sentence will look like this:
UPDATE datos SET ${1} = 0 WHERE ${1} is null
The the full SQL sentence to use is this:
call common_schema.foreach(‘SELECT c.column_name FROM information_schema.`COLUMNS` c
WHERE TABLE_SCHEMA = “inst7” and TABLE_NAME = “datos”‘,’UPDATE inst7.datos SET$ {1} = 0 WHERE ${1} is nul’);
Here you can see the process in action:
common_schema foreach
To end with, I would say that foreach is a very practical function when you need to execute repetitive task. Also, you should know that the CONCAT function (for joining strings) are not needed!!
I hope this can help you!