Update commands such as batch modification, clearing, and copying of MySQL Data Table Fields

Recently, some troubles have occurred. The newly installed official PHPwind6.0 community found that the mail addresses of many Members seem to be filled out randomly when importing the previous Member account information, in PHPwind5.5, mail address verification is not enabled. Therefore, it is estimated that many users fill in the email address randomly. Therefore, I want to ask all formal members to re-verify the email address to reactivate the member account, the results showed that the Community did not have this function at all, and it struggled for a long time. The database member data table was repeatedly installed = deleted several times, and finally found a method for batch modification.

However, this operation will even change the account of the Community creator to inactive, so if you do not know it, do not dare to do it.

In the MySql database of PHPwind6.0, its membership information is stored in pw_members, the value of the "yz" field in this data table is 1 (that is, the account has been activated) under the normal state of the member account ). If you want to activate the email again, the content displayed for this value must be the same as the value of another field "regdate, during verification, the program automatically checks whether the content of the two fields is exactly the same before performing the verification. Otherwise, a parameter error message is displayed. For example, if the value of the "regdate" field in UID 1 is 1194067635, the value of the "yz" field is 1194067635. Similarly, if the value of the "regdate" field in UID 2 is 1175320740, the value of the "yz" field is also 1175320740...

For various possible reasons, the value of the "yz" field may not be 1 (indicating that the account has been activated, how can we make the value of the "yz" field consistent with that of the "regdate" field? At the same time, because there are more than N member information, how can we batch convert the value of this field for all member accounts?

I used this method.

Updated table SET replaced field name = copied field name

Application to this example:
UPDATE pw_members SET yz = regdate

"Pw_members" indicates the name of the data table. "replaced field name" indicates the name of the field to be modified in "pw_members". Here "yz" is used "; the "Copied field name" refers to the field name that provides the field content to the "yz" field for copying (Good luck !) "Regdate"; in this way, the values of "yz" in each UID can be modified in batches and copied to the same value as "regdate. The member will ask the Member to activate the email account again upon the next login.

This is a batch modification, because all accounts, including the Community creators, will be changed to inactive, so after the execution is complete, immediately change the account that does not need to be activated, including the Community creator, back to normal. The method is to browse the pw_members data table, select the member account you want to change back to the normal state, click "edit" to enter and find the "yz" field, change the field value (for example, "1194067635") to "1", so that the member account will return to normal; you can also use an administrator account to manually activate the required member ID in "Member Management" in the community management background.

In addition, for how to manually convert the content of different fields in the same data table in batches, refer to the following command:

UPDATE table set field name = REPLACE (field name, 'original string', 'replacement string') where known field name LIKE '% original string %'

Application to this example:
UPDATE pw_members set yz = REPLACE (yz, '1', '2') where yz LIKE '% 100'

"Pw_members" indicates the name of the data table. "field name" indicates the name of the field to be modified in "pw_members". Here "yz" is used "; the "original string" is the current default value of "1" for the field "yz "; the "replaced string" refers to the new default value of "2" for the field "yz" to be modified ".

How do I batch clear the values of one or more fields?

UPDATE table set field name = '0' clear field value

Application to this example:
UPDATE pw_memberdata set money = '0' clear gold coins
UPDATE pw_memberdata set rvrc = '0' clear prestige
UPDATE pw_memberdata set credit = '0' clear contribution
UPDATE pw_memberdata set currency = '0' clear the transaction currency
UPDATE pw_memberinfo set deposit = '0' clear current account
UPDATE pw_memberinfo set ddeposit = '0' clear the time deposit
UPDATE pw_membercredit set value = '0' clear comments

Lightning blog comment: My actual usage is found on the Internet.
Mysql update (empty) the content of a certain field in a table: update table name set field name = '', not 0.