CODEWITHSUNDEEP
mysqlsubquerysql-update
Mark
Mark

Reputation: 187

Updating a table with a subquery

I'm trying to update a MySQL table and I keep getting a message stating that "you cannot use target table titles" for update in FROM clause.

This is my UPDATE statement:

UPDATE titles
SET sales = sales * 0.5
WHERE sales > (SELECT AVG(sales) FROM titles);

I'm expecting the average sales to increase by 0.5 FROM titles.

Upvotes: 1

Views: 31

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

try uisng a join with subquery 

UPDATE titles t1
INNER JOIN (
  SELECT AVG(sales) my_avg 
  FROM titles
) t2  on t1.sales > t2.my_avg
SET sales = sales * 0.5

Upvotes: 1

Related Questions