Mysql Data table field content batch modify, empty, copy and so on UPDATE command _mysql

I've had a bit of trouble lately. Newly installed PHPwind6.0 official version of the community before the introduction of the member account data, found that many members of the mail address seemingly random fill in, before the PHPwind5.5 version did not open the mail address verification function, so the estimated number of users randomly fill out the email address, so I would like to ask all the official members to retest e-mail address to reactivate the member account, the results found that the community did not have this function, struggled for a long time, the database member data table was repeatedly installed = deleted several times, finally found a batch modification method.

However, this operation will not even the Community founder's account changed to inactive, so if not clearly do not dare to do.

In the MySQL database of PHPwind6.0, its member data exists pw_members inside, the member account number in the normal state this data table "YZ" field displays the value is 1 (that is to say has activated the account number). If you need to mail activation again, this value must be the same as the value of another field "RegDate", when the program verifies that the contents of the two fields are exactly the same after the validation is done, or you give a hint of the parameter error. For example, the "regdate" field of the member UID 1 is 1194067635, and the value of the "YZ" field is 1194067635, and so on, if the "regdate" field in the Member UID 2 is 1175320740, then "YZ" The value of the field is also 1175320740 ...

At this point, for possible reasons, the value of the "YZ" field may not be all 1 (that is, the account has been activated), how to keep the value of the "YZ" field in line with the "regdate" field contents? At the same time, because the member data n many, how to batch conversion all member account of this field value?

That's the way I use it.

UPDATE table SET replaced field name = Copied field name

Apply to this article instance:
UPDATE pw_members SET yz=regdate

where "Pw_members" is the data table name; the replaced field name "refers to the name of the field that you want to modify in" Pw_members ", which is" YZ ", and" the copied field name "refers to the field name that provides the field contents to the" YZ "field (well, that's a mouthful!). "RegDate", so that the "yz" in each UID can be modified and copied in bulk to the same value sing Woo "RegDate". The next time the member logs in, the community will ask him to activate the email account again.

Since this is a batch modification, all accounts, including the community creator, will be changed to inactive, so when the execution is done, the account that includes the community creator should be changed back to normal immediately. The method is in Pw_members datasheet browsing state, select you want to change back to the normal status of the member account number, click "Edit" to enter the "YZ" field, the value of the field such as "1194067635" to "1", so that the member account will return to normal Or you can use the Administrator account to enter the community management background "member management" will be required to manually activate the membership ID.

In addition, how to manually convert the contents of different fields within the same datasheet to bulk, refer to the following command:

UPDATE table Set field name =replace (field name, ' original string ', ' substituted string ') where known field name like '% original string% '

Apply to this article instance:
UPDATE pw_members Set Yz=replace (YZ, ' 1 ', ' 2 ') where YZ like '%1% '

where "Pw_members" is the data table name; field name "refers to the name of the field that you want to modify in" Pw_members ", which is" YZ "and" The original string "is the default value" 1 "of the Field" YZ "and" replacement string "refers to the field you are about to modify YZ "New default value of 2."

How do you empty the values of one or several fields in bulk?

UPDATE table Set Field name = ' 0 ' empty field value

Apply to this article instance:
UPDATE pw_memberdata set money= ' 0 ' empty gold coins
UPDATE pw_memberdata set rvrc= ' 0 ' clears prestige
UPDATE pw_memberdata set credit= ' 0 ' empty contribution value
UPDATE pw_memberdata set currency= ' 0 ' empty trading currency
UPDATE pw_memberinfo set deposit= ' 0 ' empty current account
UPDATE pw_memberinfo set ddeposit= ' 0 ' empty term deposit
UPDATE pw_membercredit set value= ' 0 ' empty acclaim

Lightning Blog Comments: Articles on the internet to find, my actual usage
MySQL update (empty) The contents of a field in a table: Update table name set field name = ', and no 0.