MySQL Update column if value equal to any value of an array

Question

I have a table with a column that store a number. I also have 2 different array: the first one store the value to check, the second one store the new value to save in the column.
I need a query that check if the value in the column is equal to any value of the first array and if it is true update the column value with the same position value of the second array.
For example:

column value = 2
firstArray = [1, 2, 3]
secondArray = [5, 6, 7]

I want to check if 2 is in firstArray: yes... it is equal to firstArray[1] then I want to update the column value with secondArray[1] at the end of the query column value will be 6.

It is possibile? I found this MySQL: Update a columns if certain value but I'm really new in querying MySQL and I don't know how to make it work in my case.

Thanks for the help
Daniele

the database is huge (almost than 4 million records) so I think the best way is to run an SQL query in phpmyadmin. If it is not possibile I usually use php — danipen
– danipen, Commented Dec 5, 2013 at 15:24

Mihai · Accepted Answer · 2013-12-05 14:19:29Z

2

Something like this

$combined=array_combine( $1array, $2array);

First array is with keys the second with values.Docs

foreach ($combined as $key => $val) {
  if($key=2){
  $query = "UPDATE table SET Column= '$val' WHERE Column = '$key'";
  mysqli_query($query);
 }
}

edited Dec 5, 2013 at 14:19

answered Dec 5, 2013 at 14:14

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

danipen Over a year ago

Wow, thanks. I'm going to try it. There's a way to do this with a SQL query from phpmyadmin?

Mihai Over a year ago

@danipen I doubt it,but Im no SQL expert.Ill think about it.

danipen Over a year ago

It takes a while (alf an hour) to update all the table (+4000000 records) but it works fine! thanks

Bakly Over a year ago

@danipen may be you need to index you Column field

Collectives™ on Stack Overflow

MySQL Update column if value equal to any value of an array

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related