Mysql select within update

We will use the table payments in the sample database for the demonstration. You can use comparison operators e. Try It Out. For example, you can find customers whose payments are greater than the average payment using a subquery:.

For example, you can use a subquery with NOT IN operator to find the customers who have not placed any orders as follows:. When you use a subquery in the FROM clause, the result set returned from a subquery is used as a temporary table.

This table is referred to as a derived table or materialized subquery. The following subquery finds the maximum , minimum, and average number of items in sale orders:. In the previous examples, you notice that a subquery is independent. It allows you to change the values in one or more columns of a single row or multiple rows. Sometimes, you may want to update just one row; However, you may forget the WHERE clause and accidentally update all rows of the table.

See the following employees table from the sample database. In this example, we will update the email of Mary Patterson to the new email mary. Second, update the email address of Mary to the new email mary. Dan Dan 51k 36 36 gold badges silver badges bronze badges. Add a comment. Active Oldest Votes. Consp is right that it's not supported. Mark Byers Mark Byers k gold badges silver badges bronze badges. I have tried to make it a little faster, but I haven't measured the performance.

You may have to play around a bit to see what works best for your data. It's not an arbitrary whimsical restriction It's because the SELECT should result in locking the relevant rows, but the rows are already being locked by the UPDATE statement, and no one's coded the logic to do proper locking without causing the statement to potentially deadlock itself. Such request created an endless process and my server became unavailable.

This may be due to the additional functionality that I included in the request, but be careful. The transactions worked for me without any problems. Your problem is stated plainly in the MySQL manual : Currently, you cannot update a table and select from the same table in a subquery.

Conspicuous Compiler Conspicuous Compiler 6, 1 1 gold badge 36 36 silver badges 50 50 bronze badges.