Sql updating multiple rows with different values
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.Hi Site Point members I have been perusing through the solutions for "updating multiple rows with one query", but I have a pressing question: How would one "SET" multiple column values with one query?Here is my example..normal update command would be: // question ID ranges from 1-20 // $questionid == 1, $member_id==1 UPDATE table_Foo Bar SET answer One='$ans1Val', answer Two='$ans2Val', answer Three='$ans3Val' WHERE member_id='$memberid' AND question_id='$questionid'; // $questionid == 2, $member_id==1 UPDATE table_Foo Bar SET answer One='$ans1Val', answer Two='$ans2Val', answer Three='$ans3Val' WHERE member_id='$memberid' AND question_id='$questionid'; ..
As you see there shouldn’t be any problem to choose between the 3 methods – the best solution strongly depends on the source and format you have the data in and the results you are trying to achieve.To add comments, please go to the forum page for this post (guest comments are allowed for the Projects, Posts, and Updates Forums). We accept no responsibility for the contents of these comments.I am a functional person with limited SQL knowledge (mostly data extraction).So in general, I feel the INSERT method is both best and easiest to use. Make sure to save the sql_mode first if you plan on reverting it.The queries are smaller and easier to read and only take up 1 query of action. As for other comments I’ve seen that say the auto_increment goes up using the INSERT method, I tested that too and it seems to not be the case.