Update query question

[Bdog05]

What is the best way to replicate a column from one table to another table dynamically so that as values in table one change they are reflected in table 2?

I ask because I am trying to calculate values for a field based on other fields in the same table. When I try to do so I get this error:

ERROR 1093 (HY000): You can't specify target table 'table' for update in FROM clause

So I think my solution is to have a seperate table to write the value into, but in order to do that I need to replicate the key from the original table.

Anyone have any ideas?

 

[rac2]

It would be easier to help if you posted your query.

 

[Bdog05]

Sorry, I should have been more specific on what I am trying to do. Here's my table. AvgSaleslastweek is the value I am trying to write back into the table.

Date Salesperson Amount AvgSaleslastweek
1/1/05 Bob 50 50
1/2/05 Jon 100 100
1/2/05 Bob 75 62.5
1/3/05 Jon 50 75
1/4/05 Bob 250 125

So I've got a query like:

UPDATE t1 SET AvgSaleslastweek = (SELECT AVG(amount) from t1 INNER JOIN t1 as t1_1 on t1.salesperson = t1_1.salesperson where ((CurrentDate - t1.date) < 7)

 

[rac2]

Maybe this.

UPDATE DailySales t1
INNER JOIN (
             SELECT salesperson, AVG(amount) AS SalesAvg
             FROM DailySales
             WHERE (DateDiff( date, CurrentDate) < 7)
             GROUP BY salesperson
            ) t2 ON t2.salesperson = t1.salesperson
SET t1.AvgSaleslastweek = t2.SalesAvg

Also note the use of a DateDiff( date, CurrentDate) function instead of the arithmetic subtraction in the WHERE condition.