CODEWITHSUNDEEP
phpmysql
LeeG
LeeG

Reputation: 105

MySQL move data from one column to another column if data already exists in that column

I've looked through existing answers on this but can't seem to find the right one that fits, so:

I have a database populated by a php form that has a number of columns:

name | latest_figure | previous_figure1 | previous_figure2 | previous_figure3

I can update the table using a typical mysql query, but I need the data to move to the right on updating if data already exists in that field.

For example, if we have:

name=Lee, latest_figure=80

I now want to update the table with

name=Lee, latest_figure=85, I want the original figure of 80 to move into the column previous_figure1.

If we update the table again, and name=Lee has a latest_figure=88, I want the 85 to move to previous_figure1 and 80 to move to into previous_figure2, and so on.

The reason for the keeping of the data is for graphical reasons to show progress/deterioration etc.

Any help will be greatly appreciated.

Upvotes: 0

Views: 565

Answers (2)

Jaromír BaGricek Navara
Jaromír BaGricek Navara

Reputation: 136

You could try to use ON DUPLICATE KEY if primary key is set.

INSERT INTO table (name, latest_figure) VALUES("Lee", 85) ON DUPLICATE KEY UPDATE previous_figure3 = previous_figure2, previous_figure2 = previous_figure1, previous_figure1 = latest_figure, latest_figure = 85;

Hope it helps :)

Upvotes: 0

Naruto
Naruto

Reputation: 1210

I think you should rewrite your table. You should have a table that has:

name | figure | timestamp

Then you can always get the figures by name and order by timestamp so you know which is the latest. It's easier than alway making a new column.

Hope this helps.

Upvotes: 2

Related Questions