For greater detail, please follow the aforementioned link. Example table: We'll use the following table for this example:. Thursday, 21 November SQL update multiple rows with a single query, different value per row. Problem: I'd like to update many rows with each row having a possibly different value, while using only a single SQL query.
Warning: This how-to can possibly ruin a database if done wrong, so make sure that you first test it using toy data and make sure it works properly. NullUserException Nadeem Jamali Nadeem Jamali 1, 3 3 gold badges 16 16 silver badges 26 26 bronze badges. I think this is a legitimate question if someone has no knowledge of transactions yet. A better solution is provided by stackoverflow. Add a comment. Active Oldest Votes. Alex M 3, 2 2 gold badges 18 18 silver badges 23 23 bronze badges.
Marco Marco I used this example in my problem, it worked. After a long time, I came on conclusion that this type of query gives best performance on small amount of data. In case if the table has millions of records, then updating single record at a time using primary key in parameter, saves a lot of time, hence produces the better performance. Thanks, I saved the night — e-info Jason Jason 73 6 6 bronze badges.
Daniel Schneller Daniel Schneller I humbly disagree. The If-elseif-elseif etc. Of course, there are other ways to do this, as well, if one statement is really! Daniel is correct. Adriaan Koster Does this answer your question? Add a comment. Active Oldest Votes. Gordon Linoff Gordon Linoff 1. I don't understand your comment. You may have a different problem.
Thank you very much for your time. Show 12 more comments. However, this approach makes prepared statements easier to build and more concise. John 11k 11 11 gold badges 82 82 silver badges bronze badges. Trevedhek Trevedhek 3, 2 2 gold badges 17 17 silver badges 14 14 bronze badges.
Thank you! Note: This will add new rows if the key does not exist in the table resulting in unwanted records. And note that this approach require primary key is set for the table. This doesn't work if you omit any columns that can't be null, since sql still tries to create new record before actually resorting to update. Show 11 more comments.
0コメント