CODEWITHSUNDEEP
mysql
segfaulter
segfaulter

Reputation: 23

MySQL Update on query of same table

I really regret having to ask -- but I have searched all evening and have not found a solution that resolves my question. Please excuse me.

I have an update I am trying to run on a table that references the same table. I'm pretty sure I have to go about a rename, but I can't get it function correctly. Any help would be appreciated.

Here is my query, it is accepted but it doesn't modify the table.

update parts 
set price = price * .85 
where price > (select avg(price));

Thanks in advance.

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use join:

update parts p join
       (select avg(price) as avgprice from parts) pp
       on p.price > pp.avgprice
    set p.price = p.price * 0.85 ;

In an update or delete statement, MySQL does not allow you to reference the table(s) being modified in the rest of the query. It is the only database with this limitation, but it also provides the join syntax as an alternative method.

Upvotes: 2

Steve K
Steve K

Reputation: 4921

Your subquery needs a FROM statement. 

update parts p1 
set p1.price = p1.price * .85 
where p1.price > (select avg(p2.price) from parts p2);

Without the 'from' it uses your current column, and since you are not grouping by anything, it does average of the current column's value, which is itself, and by definition is not greater than itself.

Also, give the table names aliases so the db doesn't mix them up.

Upvotes: 0

Related Questions