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?
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.