MYSQL UPDATE with nested SELECT from different databases

Question

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

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: stackoverflow.com/questions/4268416/… — mitkosoft
– mitkosoft, Commented Mar 30, 2016 at 15:10

Gordon Linoff · Accepted Answer · 2016-03-30 15:08:50Z

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.

answered Mar 30, 2016 at 15:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL UPDATE with nested SELECT from different databases

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related