Mysql change one column value based on another column

Question

I have an auto increment column. I want to change value of another column based on this auto increment column value.

another column value  ==> (auto increment column value/3) +1

How do I do it via query? Is it possible?

I want to do it for newly creating rows.

Do you want to modify it on _existing_ rows in your table, or to ensure that newly inserted rows have the derived value? — Michael Berkowski, Oct 24 '15 at 13:07
It wasn't mine, but I suspect it is because you did not show previous attempts or give context for when and how you need to do this action (which is why I asked). — Michael Berkowski, Oct 24 '15 at 13:21
@MichaelBerkowski I don't know what I can try with this. Its a automatic value. And the same time of auto value creation, I have to change another column value. — Gibbs, Oct 24 '15 at 13:24
It can be done with a TRIGGER or more easily in PHP code (if it is certain new rows will never be inserted outside of PHP). If you post a bit of your PHP code where the INSERT is done, both suggestions can be expanded. — Michael Berkowski, Oct 24 '15 at 13:26
I am using mysqli prepared statements. And scenario is bit complex. My form is data upload form. So arbitrary number of rows. So I have to dynamically prepare. — Gibbs, Oct 24 '15 at 13:29
Okay, given that it's an unknown number of rows, a trigger would be appropriate to use... I can't remember though if MySQL allows an after insert trigger on the _same_ table. I'll have to dig into the docs. — Michael Berkowski, Oct 24 '15 at 13:36
@MichaelBerkowski I think a trigger might be overkill if you take into consideration my answer, check it out perhaps? — Paul Stanley, Oct 24 '15 at 13:54
I' m curious if my answer worked for the INSERT version of the question. — Kostas Mitsarakis, Oct 24 '15 at 18:38

score 3 · Answer 1 · answered Oct 24 '15 at 13:53

3

Is the new (id/3)+1) value ever going to change?

Judging from the question, the data only exists at the time of the insert. The auto increment column will never change as it has to be a Primary Key, I would only insert on it if you have to join on it or if it is going to change in future. You can get this derived value from a SELECT query after your insert.

SELECT id, ((id/3)+1) as derived value from table;

answered Oct 24 '15 at 13:53

Paul Stanley

4,018
6
35
56

If it has to change, then I can modify my PHP statements. Yes Your assumptions are correct – Gibbs Oct 24 '15 at 13:54
What are you using this new value for? – Paul Stanley Oct 24 '15 at 13:55
To identify test Id. First 3 questions belongs to test1. (0/3)+1,(1/3)+1,(2/3)+! – Gibbs Oct 24 '15 at 13:56
Can you give us the structure of your question and test tables please, I think you are going about this the wrong way. – Paul Stanley Oct 24 '15 at 13:57
Its a big trial. I have variety of subs. Each subs have questions table for learning and test table for test purpose. – Gibbs Oct 24 '15 at 14:00
For most applications I would probably derive it at query time myself too. It would only be of benefit to calculate and store it at insert if the derived value had to be queried against often, or with very high row volume, because it would be possible to index it. – Michael Berkowski Oct 24 '15 at 14:08
@MichaelBerkowski thanks for your input. I now believe the problem is a bit more simple, I think the tables just need to be used correctly. Tests have questions with answers. gopsAB, this doesn't sound like a tricky problem, but I think introducing any code mentioned in these answers will make it one. – Paul Stanley Oct 24 '15 at 14:16

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

It is a "derived" value and you should consider not to store this value but calculate it in your PHP code every time you need it. Here is a post where you can find further explanations about when to store derived values: Storing “derived” values vs calculating them on extraction

@Octopi solution could solve your problem.

edited May 23 '17 at 12:24

Community

1
1

answered Oct 24 '15 at 13:37

Luis Teijon

4,769
7
36
57

+1, I have used a segment of your answer in my answer because I think you should do it all in mysql as opposed to leaving it to php. – Paul Stanley Oct 24 '15 at 13:53
1

You're right... but I was assuming that the mysql query could be written inside a PHP class. – Luis Teijon Oct 24 '15 at 15:28

score 0 · Answer 3 · edited May 23 '17 at 12:09

Try the following:

INSERT INTO your_table (your_column) 
    SELECT (MAX(auto_increment_column) / 3) + 2 FROM your_table;

EDIT

If you want to calculate the column depending on the number of values use this:

INSERT INTO your_table (your_column) 
    SELECT (COUNT(*) / 3) + 2 FROM your_table;

EDIT 2

You can accomplish the same using:

INSERT INTO your_table (your_column) VALUES ( 
    (SELECT (MAX(aa.auto_increment_column) / 3) + 2 FROM your_table AS aa) 
);

EDIT 3

For more columns you can use something like this:

INSERT INTO your_table (your_column, some_other_column) VALUES ( 
    (SELECT (MAX(aa.auto_increment_column) / 3) + 2 FROM your_table AS aa), some_other_column 
    );

You can find more info here: Select from same table as an Insert or Update

In fact you have to add +2 because it's 1 from you rule and +1 to increment the MAX(id).

Mysql change one column value based on another column

3 Answers3