Update query question

[Bdog05]

Can anyone tell me what is wrong with this?

UPDATE sales t2
INNER JOIN
(SELECT (t1.commission rate * t2.amount) as commission
FROM salesreps t1, sales t2)
t1 ON t1.rep = t2.rep
SET t2.commission = commission;

I get this error.

ERROR 1054 (42S22): Unknown column 't1.rep' in 'on clause'

 

[swampBoogie]

Why update in the first place? You are only adding redundant data and how do you know that the commission is always up to date?

Anyhow, you are better off using standard SQL instead of som propietary stuff.

UPDATE sales
set commission = amount*(select commission_rate
from salesrep 
where salesrep.rep = sales.rep)

(Your error is due to that the rep column is not included in the derived table. Your query is also unneccesary complex

UPDATE sales t2
INNER JOIN
salesreps t1
t1 ON t1.rep = t2.rep
SET t2.commission = commission_rate*amount

should be sufficient. (Everything untested.)