Checking for and modifying the value of one column in all rows

Other Tech Topics
1

I am probably not in the best place to ask this question as it pertains to MySql rather than SQL but the principles are the same.
Due to a recent edit in the software that builds the tables we ended up with a few cases where a default value that should be "1" ended up as "0" for every record created in that DB.
And we have at least 40 or more DB's in constant use with new ones created as needed. While i think i have resolved the cause so it will not happen going forward, i have to check each of the existing DB's to ensure that every record shows a "1" instead of a "0" in those locations and manually change it.
Not a huge undertaking and i have probably already resolved a those incorrect entries.
But in modifying each of them one by one, i wondered if there existed a script which could be set to a selected 'column-name', test each that column entry for each row of the DB and replace it withthe correct data if need be.
Such as if value == "1" replace with "1" (the only other value I ran across was a "0" but since every entry should be "1" I thought i would cover all bases
Taking this one step further, it would be even better if i could perform the same test and replace if needed on every DB in use.
PHPMYADMIN works very well for making changes line by line and this has never happened before, but if it ever did occur in the future (or anything like it) I would like to know if there is a way to check for and modify the values of all of them in every DB at once.

2

I would run a command like shown below, copy its output to another query window and execute it.

select 
	'UPDATE ' +
	TABLE_SCHEMA +
	'.' +
	TABLE_NAME +
	' SET ' +
	COLUMN_NAME +
	' = 0  WHERE ' +
	COLUMN_NAME +
	' <> 0;'
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE
	COLUMN_NAME = 'YourColumnName';

INFORMATION_SCHEMA may be available in MySQL as well, or there should be a similar command to list all columns in all tables. You should take care to eliminate any rows that correspond to system tables.

As for looping through all databases, SQL Server has sp_MSforeachdb procedure. There may be something similar in MySQL as well.

3

Sadly, in this case, I think the differences are likely to be significant.

In MS SQL there are tables for Database (which would allow iterating through all databases), Tables (within each DB) and Columns. There is also the generic INFORMATION_SCHEMA.COLUMNS which may well also exist in MySQL, but personally I prefer to use MS SQL's own tables for this job (I've got caught out by permissions in INFORMATION_SCHEMA Views restricting me from some, but not all, :frowning: of the rows in the past ...

Just in case [YourColumnName] might allow NULLs I would suggest you widen @JamesK query to

' = 0  WHERE ' +
	COLUMN_NAME +
	' <> 0 OR ' +
	COLUMN_NAME + ' IS NULL;'

In MS SQL there is a function QuoteName() which can be used with column names, and the like, in this scenario to ensure that any weird! column names are appropriately quoted. My recollection is that column names in MySQL are quoted with back ticks? (whereas in MS SQL it is square brackets ... another differences ...) and that might be needed unless the [YourColumnName] is straightforward.

Thus you might be better off asking in a MySQL forum :toast: