0

I want to update rows on a table which contains the following colums:

    `parameter_name`(PRIMARY KEY),
    `option_order`,
    `value`.

I have a collection called parameterColletion which contains "parameterNames", "optionOrders" and "values". This collection does not have a fixed value, it can receive the quantity of parameters you want to.

Imagine I have 5 parameters inside my collection (I could have 28, or 10204 too) and I am trying to update the rows of the database using the next query. Example of query:

UPDATE insight_app_parameter_option
SET option_order IN (1,2,3,4,5), value IN ('a','b','c','d','e') 
WHERE parameter_name IN ('name1', 'name2', 'name3', 'name4', 'name5')

But this isn't doing the job, instead it gives back an error which says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (1,2,3,4,5), value IN ('a','b','c','d','e') WHERE parameter_name IN ('name1'' at line 2

1,2,3,4,5 -> Represent the option orders inside parameterCollection.

'a','b','c','d','e' -> Represent the values inside parameterCollection.

'name1', 'name2', 'name3', 'name4', 'name5' -> Represent the names inside parameterCollection.

I know how to update each parameter by separate but i would like to do it all together. Here are some links I visited where people asked the same question but they used a fixed colletion of objects, not a mutable one.

MySQL - UPDATE multiple rows with different values in one query

Multiple rows update into a single query

SQL - Update multiple records in one query

SanQuinteros
  • 344
  • 4
  • 14

1 Answers1

1

That's not possible with MySQL. The error you are receiving is a syntax error. You are not able to set multiple values at once. This is the correct syntax to a UPDATE statement: (ref)

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

You need to create separate UPDATEs for each row. I suggest executing all in a single transaction, if its the case.

The correct syntax for your example is:

UPDATE insight_app_parameter_option
SET option_order = 1, value = 'a'
WHERE parameter_name = 'name1';

UPDATE insight_app_parameter_option
SET option_order = 2, value = 'b'
WHERE parameter_name = 'name2';

UPDATE insight_app_parameter_option
SET option_order = 3, value = 'c'
WHERE parameter_name = 'name3';

...
Samuel Torga
  • 175
  • 3
  • 11
  • 1
    So if i have 133 parameters i have to go through my parameterColletion to update each parameter separately. If that what you meant thanks for the response, you don't have to add nothing else. If that is not what you intended with your response I request you to explain to me with other words what to do. – SanQuinteros Jul 23 '21 at 19:08
  • 1
    That's correct. The "IN" is not available on SET because MySQL can't match the positions with the WHERE clause. You need to create N UPDATE statements for each object. – Samuel Torga Jul 23 '21 at 19:43