I was wondering if you can do an Update with multiple conditions like so: Show
participantes -> Table confirmado -> field of the table participantes.
Farmer Joe 5,8801 gold badge27 silver badges38 bronze badges asked Apr 30, 2014 at 17:05
1 MySQL's AND clause will only work when ALL of the criteria are met. What you're looking for is OR. In the format that you provided:
Although, the above-noted IN (19, 20, 21) would be better for this specific use case. Your original query was trying to look for a single row where id was simultaneously 19, 20, and 21, which would never happen. answered Apr 30, 2014 at 17:29
NoahNoah 1,84714 silver badges19 bronze badges To accomplish what you're describing, I would, instead, use the
answered Apr 30, 2014 at 17:07
AaronAaron 5,0171 gold badge17 silver badges20 bronze badges 1 How about this?
Stephen Rauch♦ 45.6k30 gold badges105 silver badges126 bronze badges answered Oct 11, 2018 at 3:09
1 In MySQL, you can use tuple comparison:
That looks nice and succinct, although, as ypercubeᵀᴹ mentioned in a comment, it may not work well performance-wise. However, given how the conditions are re-used in your UPDATE statement, you could also take a different approach altogether: represent the affected IDs and the new values as a derived table and use an update with a join:
This way the MySQL UPDATE StatementLast update on August 19 2022 21:51:22 (UTC/GMT +8 hours)UPDATE TableThe MySQL UPDATE statement is used to update columns of existing rows in a table with new values. Version: 5.6 Syntax : Single table: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple tables: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] Arguments
The UPDATE statement supports the following modifiers:
Following are some examples on MySQL update where we have used newpurchase as sample table. Sample table: newpurchase MySQL UPDATE column MySQL UPDATE column can be used to update some specific columns. The following MySQL statement will update the 'receive_qty' column of newpurchase table with a new value 20.
MySQL UPDATE with WHERE MySQL UPDATE command can be used with WHERE clause to filter (against certain conditions) which rows will be updated. The following MySQL statement will update the 'receive_qty' column of newpurchase table with a new value 25 if the value of purch_price is more than 50.
MySQL UPDATE using NULL MySQL UPDATE command can be used to update a column value to NULL by setting column_name = NULL, where column_name is the name of the column to be updated. The following MySQL statement will update pub_lang column with NULL if purch_price is more than 50. In this statement, other columns are also updated with respective new values.
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. The following MySQL 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.
MySQL UPDATE with subqueries Here in the following, we have discussed how to use MySQL UPDATE command with subqueries. The following MySQL statement will update purch_price with purch_price multiplied by 5 if it satisfies the condition defined in the subquery started with SELECT wrapped within a pair of parenthesis. The subquery retrieves only those cate_ids from purchase table if their corresponding receive_qty is more than 10.
Updating MySQL Table using PHP Script You can update MySQL table data (using UPDATE command) through a PHP script. Within the script, PHP function MySQL_query() execute the SQL command. We have used a table called 'item' to apply the query: PHP Script
Sample Output: Multiple Updates in MySQL 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:
Pictorial presentation: Sample Output: Examples: MySQL UPDATE on multiple tablesHere we have used two tables book_mast and purchase for the following exampleas sample table. We have shown some of the columns in the associated tables. Here are the tables below - MySQL> SELECT book_id,book_name,pub_lang,book_price -> 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 MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> 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 -
After updating it is to be shown that, the highlighted rows have been effected in both the tables. MySQL> SELECT book_id,book_name,pub_lang,book_price -> 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 MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> 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) MySQL: 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 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. Code:
Explanation Output: Previous: INSERT with
LEFT JOIN How do I UPDATE multiple values in MySQL?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.
How do you UPDATE a table with multiple conditions?The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.
How do you UPDATE multiple columns in SQL with different conditions?To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
How UPDATE multiple rows in SQL with different values?Update Multiple Columns in Multiple Records (Rows) With Different Values in MySQL. Use the CASE statement.. Use the IF() function.. Use INSERT ... ON DUPLICATE KEY UPDATE .. Use UPDATE with JOIN() .. |