2

I have two databases: zeus and hades. Both databases have a field called wxrecord. On Hades the wxrecord is a Unix timestamp.

I create a new record in zeus.alldata using a SELECT/INSERT from hades.wxdata. This includes the wxrecord from hades.wxdata.

Once the new record is created on zeus.alldata, I need to run some math calculations and then update a single column with the output of that calculation.

I would like to update zeus but only update the specific record on zeus that corresponds to its counterpart record in hades. I want to match the max wxrecord on both and update so long as they are equal.

Here is what I have tried:

UPDATE zeus.alldata SET A='50' WHERE (SELECT max(wxrecord) FROM zeus.alldata)  = (SELECT max(wxrecord) FROM hades.wxdata);

I keep getting this error:

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

Any help would be appreciated

Richard
  • 313
  • 1
  • 4
  • 14
  • In general you cannot UPDATE and SELECT one and the same table within one query like that. You can try to make `INNER JOIN` of `alldata` table into the query. See more here: http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql – mitkosoft Mar 30 '16 at 15:10

1 Answers1

1

In any case, I presume you actually want this query:

UPDATE zeus.alldata
    SET A = '50'
    WHERE wxrecord  = (SELECT max(wxrecord) FROM hades.wxdata);

This will update the record in zeus that has the maximum record value in hades.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786