Updating a table in mysql
Suppose I want to update a table with data from a subquery that refers to the same table.
I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using non-standard syntax, and so on.
In this statement, other columns are also updated with respective new values.
My SQL UPDATE multiple columns My SQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value.
Within the script, PHP function My SQL_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.
The WHERE clause specifies which record(s) that should be updated.
If you omit the WHERE clause, all records in the table will be updated!
bval2 of table12 must be matched with the cval1 of table13 - then the following code can be used.
There may be a requirement where the existing data in a My SQL table needs to be modified. This will modify any field value of any My SQL table.
My SQL UPDATE with subqueries 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.