Multiplying a whole column's entries in mysql

Hi I have 15 rows of data (user entries), each with 100 fields (answers to a survey via a radio button 1-6).

Every answer from every respondent needs to be multiplied by either a factor of +1 or -1

From here I need the new positive or negative value to reside in the same table (or a new copy it doesn't really matter)

Question: What's the best way to handle this type of calculation?

(There are about 30 开发者_如何学编程answers that need to be multiplied by -1).

Firstly, your design is not really normalised - that would involve having all the answers in one column, probably with user and question number as a composite primary key. That organisation would make this a lot easier.

As it is, your query needs to look like:

update responses set
  answer_2 = -1 * answer_2,
  answer_3 = -1 * answer_3,
  answer_4 = -1 * answer_5,
  answer_7 = -1 * answer_7;

Where those column names are the columns you need to negate.

Given that there need to be thirty assignment lines in the query, i suggest you make use of copy and paste, or a couple of lines of shell script. Thirty isn't enough to turn to anything more complicated than that, though.