Here in the following we have discussed how to use My SQL UPDATE command with subqueries.The following My SQL 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-84

In this statement, other columns are also updated with respective new values.

My SQL 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 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.

Within the script, PHP function mysql_query() execute the SQL command.

We have used a table called 'item' to apply the query: Table Name : item Structure : item_code varchar(20), value int(11), quantity int(11) where item_code is the primary key.In the following rows of item table, 'value' column which is marked with red rectangle will be updated.My SQL UPDATE command can be used with WHERE clause to filter (against certain conditions) which rows will be updated.The following My SQL 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.My SQL 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 My SQL statement will update pub_lang column with NULL if purch_price is more than 50.