Sql updating multiple rows with different values
UPDATE ============================================= So here’s perhaps a better idea in most of the cases, use the CASE WHEN ELSE END syntax of My SQL to update multiple rows in the options table of your web application: because if you don’t, the other rows not mentioned here will be updated to have empty values.Update multiple records in a table by `ID` ============================================= Below is an example of updating more than one rows in a table by the id field.(pk_vals, update_vals) ON DUPLICATE KEY UPDATE update_col=VALUES(update_col), …update_col=VALUES(update_col); OPTIMIZE TABLE tbl_name; Example as per article: INSERT INTO categories (id, display_order, title) VALUES (1, 3, ‘New Title 1’), (2, 4, ‘New Title 2’), (3, 5, ‘New Title 3’) ON DUPLICATE KEY UPDATE display_order=VALUES(display_order), title=VALUES(title); OPTIMIZE TABLE tbl_name; The advantage of this solution is that it’s driven by the indexing of the primary keys, on which a CASE cannot rely.Use the query below: To transfer all the new values you have in the temporary table to the original table.
There comes a time when you need to update multiple rows with different values.
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.
And the last thing – once you have chosen the appropriate option, you may want to run these methods or inside a stored procedure.
My conclusion is that that the query runs as if there were two different queries running over the bunch of rows resulted after evaluating the WHERE condition, the second CASE operator applying over the data modified by the first CASE operator: 1.
WHERE condition is evaluated; resulted data set: idnode idnode_parent position 3 1 1 4 1 2 9 7 1 2.