Mysql updating multiple rows

22 Oct

As an alternative to an ELSE value, you can provide a WHERE clause: UPDATE `table` SET `col` = CASE `id` WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ WHEN 3 THEN ‘three’ END WHERE `id` IN (1, 2, 3) Same result but without doing a no-op update no rows you don’t care about.

Here’s a much more efficient My SQL-flavored solution: INSERT INTO tbl_name (pk_cols, update_cols) VALUES (pk_vals, update_vals), …

the id of table11 and table13 must be matched, and 2).

bval2 of table12 must be matched with the cval1 of table13 - then the following code can be used.

In the following rows of item table, 'value' column which is marked with red rectangle will be updated.

PHP Script Sample Output: Multiple Updates in My SQL Sample table: table1 Problem If you want to update the val1 with 5,8 and 7 for concerned id 1,3 and 4 and the other val1 will remain same and the val2 will be updated with 13 and 5 for the concerned id 2 and 4 and the other will remain same, the following update statement can be used by using IF and CASE.

Code: FROM book_mast; -------- ------------------------------------ --------- ---------- | book_id| book_name | pub_lang|book_price| -------- ------------------------------------ --------- ---------- | BK001 | Introduction to Electrodynamics | English | 85.00| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 250.00| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| -------- ------------------------------------ --------- ---------- 16 rows in set (0.00 sec) and My SQL FROM purchase; --------- ---------- ------------- ------------ | book_id | pub_lang | purch_price | total_cost | --------- ---------- ------------- ------------ | BK001 | English | 75.00 | 1125.00 | | BK004 | English | 55.00 | 440.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 35.00 | 525.00 | | BK001 | English | 25.00 | 200.00 | | BK003 | Hindi | 45.00 | 900.00 | --------- ---------- ------------- ------------ 6 rows in set (0.02 sec) If we want to update the book_price of the table book_mast by an increment of 5% and also update the purch_price and total_cost of puchase table by an increment of 5%, and this increment will affect only those rows in both book_mast and purchase table, which publishing language is English and book_id matching in both the tables, we can write the following code - UPDATE book_mast,purchase SET book_mast.book_price=book_mast.book_price (book_mast.book_price*.05), purchase.purch_price=purchase.purch_price (purchase.purch_price*.05), purchase.total_cost=receive_qty*(purchase.purch_price (purchase.purch_price*.05)) WHERE book_mast.book_id=purchase.book_id AND purchase.pub_lang="English"; FROM book_mast; -------- ------------------------------------ --------- ---------- | book_id| book_name | pub_lang|book_price| -------- ------------------------------------ --------- ---------- | BK001 | Introduction to Electrodynamics | English | 89.25| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 262.50| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| -------- ------------------------------------ --------- ---------- 16 rows in set (0.01 sec) and My SQL FROM purchase; --------- ---------- ------------- ------------ | book_id | pub_lang | purch_price | total_cost | --------- ---------- ------------- ------------ | BK001 | English | 78.75 | 1181.25 | | BK004 | English | 57.75 | 462.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 36.75 | 551.25 | | BK001 | English | 26.25 | 210.00 | | BK003 | Hindi | 45.00 | 900.00 | --------- ---------- ------------- ------------ 6 rows in set (0.08 sec) My SQL: Update with Join Statement Sample tables Problem If we want to update the aval1of table11 with the bval1 of table12 against the following condition - 1).

The following My SQL statement will update receive_qty, pub_lang, and receive_dt columns with new values 20, Hindi and 2008-07-10 if purch_price is more than 50.

My SQL UPDATE with subqueries Here in the following, we have discussed how to use My SQL UPDATE command with subqueries.