Modifying column data and adding that data to new column

[Live Free]

I have a xf_user column that has data from an old custom add-on. I'd like to modify the number value of this field and then import it into a column for a new add-on. I'm not familiar with SQL commands, but after a little research, this is what I came up with:

UPDATE xf_user set post_quota = post_quota *.01

Takes old add-on data, where users have earned a post credit, and reduces by 100x. (necessary due to how old add-on counted data). 8000 credits becomes 80 credits, and so on.

UPDATE xf_user SET db_credits = post_quota WHERE xf_user.post_quota > 0

Copies data from old post_quota to new column.

I'm sure this is quite crude, am I close? I could use some guidance. Much thanks.

EDIT: first column ins INT(10), second column is double.

 

[nocte]

I am not a MySQL pro, but you can do this in 1 query, I guess:

UPDATE xf_user set db_credits = post_quota *.01

You can clone the xf_user table in order to test it without running into problems if anything does not work.

 

[Live Free]

@nocte this is very helpful thank you. I imagine best way to test is clone table, run query, and compare data for unexpected results?

What about the fact original column is INT(10) type and secondary column is DOUBLE?

 

[nocte]

What about the fact original column is INT(10) type and secondary column is DOUBLE?

I don't think that's a problem.

I imagine best way to test is clone table, run query, and compare data for unexpected results?

Yeah, you can also clone the column db_credits (i.e. create a column like db_credits_test) and run the query. I think, that's the easiest way and you cannot ruin anything, as long as your test column has a default value (normally 0; otherwise you might get server errors).