0

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 incremental gkey field).

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, I get the

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

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 > aa.gkey))
where course_date = '1970-01-01'

Any ideas on how I can pull this off?

I want to make clear that 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.

Jonas
  • 121,568
  • 97
  • 310
  • 388
Martin Ocando
  • 914
  • 2
  • 8
  • 18
  • No, is not answered there. That was about a simple subquery. I'm trying to look up the next record referencing the main table. How is that answered in that thread? – Martin Ocando Oct 27 '18 at 00:42

1 Answers1

0

You can try to use table name instead of alias name, because inner subquery didn't know alias name aa

update BI.fact_training_event_tbl
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'
D-Shih
  • 44,943
  • 6
  • 31
  • 51