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