Multiplying a whole column's entries in mysql

Question

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).

Answer 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.

Answer 2

I have a much faster solution:

Instead of Multiplying by -1, how about using subtraction ???

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

Subtraction is always faster than multiplication. This should yield the desired results as well.

http://www.linkedin.com/in/rolandoedwards