MySQL UPDATE statement question

[Ben E Lou]

I'm trying to apply a simple weighting to update a column in a MySQL table as follows:

update fof7_rookies set adjustedgrade=
  (SELECT round(adjustedgrade*w.weight,0)
  FROM fof7_rookies r
  JOIN fof7_positionweights w on w.positiongroup=r.position_group)
where rookieyear<2042

It's giving me "You can't specify target table 'fof7_rookies' for update in FROM clause."

I suppose that I can add a column, write the value to that column, set adjustedgrade to equal that column, then delete the column, but I'm guessing there's a more straightforward way to do this.

Little help?

[cartman]

What happens when you try this?

update fof7_rookies set adjustedgrade=
  (SELECT round(adjustedgrade*w.weight,0) as weighting
  FROM fof7_rookies r
  JOIN fof7_positionweights w on w.positiongroup=r.position_group)
where rookieyear<2042

[cuervo72]

something like:

UPDATE fof7_rookies r, fof7_positionweights w
SET r.adjustedgrade = round(r.adjustedgrade * w.weight, 0)
WHERE r.position_group = w.position_group
AND r.rookieyear < 2042

OR

UPDATE fof7_rookies
JOIN fof7_positionweights w on w.positiongroup = r.position_group)
SET r.adjustedgrade = round(r.adjustedgrade * w.weight,0)
WHERE r.rookieyear < 2042

I think.

ETA: if not, look at this, this should help - http://www.mysqltutorial.org/mysql-update-join/

[cartman]

actually, yeah, cuervo's second query should be what you want for MySQL, except for the extraneous ) at the end of the 2nd line, and the missing alias of r for the first line

[Ben E Lou]

Cartman's solution gave me the same error. Trying cuerv's now...

[Ben E Lou]

Cuervo suggestion 1 appears to have worked. Thanks to both of you!

[cuervo72]