Error referencing main table on lookup subquery for update

Question

First of all, this question is not answered earlier since the thread about error 1093 shows a simple subquery. In my case, I'm doing a lookup for the next record referencing the main table. Please don't tag it as a duplicate without first reading the whole question.

I need to update the records of a table which has a wrong date (1970-01-01), using the next record's data (according to the gkey field, which is consecutive int primary key).

So, if I do this query:

SELECT aa.gkey,
       aa.course_date,
       (select course_date from BI.fact_training_event_tbl bb where bb.gkey = (
            select min(cc.gkey) 
            from BI.fact_training_event_tbl cc 
                 where cc.gkey > aa.gkey)) as next_date
from BI.fact_training_event_tbl aa
where course_date = '1970-01-01'

It brings the records correctly, as expected:

gkey   course_date  next_date
====   ===========  =========
4103   1970-01-01   2017-03-23
4884   1970-01-01   2017-03-22
5047   1970-01-01   2017-03-23

I now need to update the course_date field with next_date, but if I try running the following:

update BI.fact_training_event_tbl aa
    set course_date =
    (select course_date from BI.fact_training_event_tbl bb where bb.gkey = (
            select min(cc.gkey)
    from BI.fact_training_event_tbl cc
         where cc.gkey > BI.fact_training_event_tbl.gkey))
where course_date = '1970-01-01'

I get the error:

Error Code 1093. You can't specify target table 'BI.fact_training_event_tbl' for update in FROM clause

I tried doing what is recommended here: MySQL Error 1093 - Can't specify target table for update in FROM clause, nesting the query inside another:

update BI.fact_training_event_tbl as zz
    set course_date =
    (select course_date from
    (select course_date from BI.fact_training_event_tbl as bb where bb.gkey = (
            select min(cc.gkey) 
      from BI.fact_training_event_tbl as cc
           where cc.gkey > gkey)) as aa )
where course_date = '1970-01-01'

but all I get is set the date_course as null, and not the next_date.

And if I try referencing the main table like this:

where cc.gkey > BI.fact_training_event_tbl.gkey

or

where cc.gkey > zz.gkey

It says: Unknown column BI.fact_training_event_tbl.gkey or zz.gkey.

Any ideas on how I can pull this off?

score 2 · Accepted Answer · answered Oct 27 '18 at 19:46

The underlying reason for the 1093 error is that MySQL cannot access the table you want to update a second time with any direct dependency on that table.

Even though the workarounds you linked look like they just add a select-layer around the original subquery, e.g. select * from (your original subquery), you missed the reason why they work: they use a derived table instead of a (dependent) subquery (which is what @Cheekysoft meant with implicit temporary table in your linked answer). A derived table cannot depend on the outer query (so the underlying problem is gone). It is more or less treated like any actual table (notice e.g. that you have to name a derived table, in your case aa; see e.g. another answer of mine for some more details about this).

But this also means that you cannot use any dependency on the outer table here, no matter how you want to trick MySQL into doing so. You e.g. get the unknown column-error because the outer query is just not accessable for reference at this point.

So the basic strategy is to put all rows you will ever need into your derived table and then do a join to select the row that you need to update the actual row:

update fact_training_event_tbl
join ( your original select that returns 3 rows ) base
on base.gkey = fact_training_event_tbl.gkey
set course_date = base.course_date

"Inside" the derived table (base), you can do whatever you want and use fact_training_event_tbl as often as you want, but the dependency to the outer fact_training_event_tbl is done "outside" of base by the on-condition.

Since not only base is a (derived) table, but fact_training_event_tbl is also an (actual) table, it is generally also possible to do

update fact_training_event_tbl
join fact_training_event_tbl base
on base.gkey = fact_training_event_tbl.gkey + 1
set course_date = base.course_date

In your case, this would work if you meant "gkey field, which is consecutive int primary key" literally (so without gaps). But even if not, it should illustrate the analogy between the use of a normal and a derived table in this situation.

Brilliant. I'm OOF for the weekend, but I'll test it first thing Monday morning and report back. This is exactly what I needed. Thank you very much for your help. — Martin Ocando, Oct 27 '18 at 23:05
Outstanding! It worked like a charm. The final query below. Thank you very much. — Martin Ocando, Oct 29 '18 at 15:41

score 0 · Answer 2 · answered Oct 29 '18 at 15:40

Reporting back the final query that worked, thanks for solarflare for the solution:

update fact_training_event_tbl orig
join ( SELECT aa.gkey,
    aa.course_date,
    (select course_date from BI.fact_training_event_tbl bb where bb.gkey = (
            select min(cc.gkey) from BI.fact_training_event_tbl cc where cc.gkey > aa.gkey)) as next_date
from BI.fact_training_event_tbl aa
where course_date = '1970-01-01' ) base
on base.gkey = orig.gkey
set orig.course_date = base.next_date

Error referencing main table on lookup subquery for update

2 Answers2