MySQL Update Subquery problem

Question

I am stuck on an update query that is using a subquery and have not been able to figure it out after reading the manual and trying different ideas. Below is the table & query. Tables Temp_2, Temp_3 & Temp_4 both have 33 rows in them and no null values.

Any ideas on how to resolve this?

CREATE  TABLE  temp_2 (
    date_value date default NULL, 
    close_adj_value_1 double default NULL);

CREATE  TABLE  temp_3 (
    date_value date default NULL, 
    first_close_adj_value_1 double default NULL);

CREATE  TABLE temp_4 (
    date_value date default NULL,
    pct_return_1 double default NULL);

INSERT INTO temp_4 (date_value) SELECT date_value FROM temp_2;

UPDATE  temp_4
SET     pct_return_1 = 
    (SELECT ((temp_2.close_adj_value_1 / temp_3.first_close_adj_value_1) - 1)
    FROM   temp_2,temp_3
    WHERE temp_2.date_value = temp_3.date_value);

Thanks, Eric

Answer 1

Since you're using MySQL, you can employ its multi-table UPDATE syntax:

UPDATE temp_4
 JOIN temp_2 USING (date_value)
 JOIN temp_3 USING (date_value)
SET temp_4.pct_return_1 = (temp_2.close_adj_value_1 /
                           temp_3.first_close_adj_value_1) - 1;

I assume you want to use the date_value column to correlate the rows in temp_4 to the rows in the other tables.

An alternative solution you could use is to insert all the values into an empty temp_4 table in one go:

INSERT INTO temp_4 (date_value, pct_return_1)
  SELECT temp_2.date_value, 
    (temp_2.close_adj_value_1 / temp_3.first_close_adj_value_1) - 1
  FROM temp_2 JOIN temp_3 USING (date_value);

Answer 2

The answer from Bill shown below solved my problem:

INSERT INTO temp_4 (date_value, pct_return_1) SELECT temp_2.date_value, (temp_2.close_adj_value_1 / temp_3.first_close_adj_value_1) - 1 FROM temp_2 JOIN temp_3 USING (date_value);