I have a php form into which I need to populate 5 fields from one MySQL table after choosing an item from a dynamic drop down select list (from MySQL database). Updating multiple columns in single MySQL table. I am making a program using PHP/MySQL. I need to update 3 columns in a table. When i use a single UPDATE-SET. MySQL UPDATE multiple columns MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.
For instance, three updates into 1 query:
I read an example, but I really don't understand how to make the query. i.e:
I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?
Adriaan KosterYou can do it this way:
I don't understand your date format. Dates should be stored in the database using native date and time types.
Gordon LinoffGordon LinoffMySQL allows a more readable way to combine multiple updates into a single query. This seems to better fit the scenario you describe, is much easier to read, and avoids those difficult-to-untangle multiple conditions.
This assumes that the user_rol, cod_office
combination is a primary key. If only one of these is the PK, then add the other field to the UPDATE list.If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.
However, this approach makes prepared statements easier to build and more concise.
Adriaan KosterYou can use a CASE
statement to handle multiple if/then scenarios:
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
How to set multiple columns of a table using update query in mysql?
DavidJust add parameters, split by comma:
See also: mySQL manual on UPDATE
Pekka 웃Pekka 웃