Oracle SQL Update on Inner Join

Question

Stuck on something I a bit simpler this morning.

I have the following issue with readings and Daylight savings times going a bit mad.

I need to set OLD = NEW in the following.

select d.t0100 as tab1,
       t.t2400 as tab2
    from reportro.non_dst_readings d
        inner join reportro.dst_readings t
            on d.cust = t.cust
            and to_date(d.readdate_ndst, 'dd-mmm-yy') 
                 = (to_date(t.readdate, 'dd-mmm-yy') -1);

^^ But the above select isn't working, much less the update.

I was going to try to update them by:

update (
select d.t0100 as tab1,
       t.t2400 as tab2
    from report.non_dst_readings d
        inner join report.dst_readings t
            on d.cust = t.cust
            on to_date(d.readdate_ndst, 'dd-mmm-yy') 
                 = (to_date(t.readdate, 'dd-mmm-yy') -1)
        )
 set old = new;

Any ideas?

Thanks again, I'd be so lost without StackOverflow :)

Answer 1

NEW is a keyword in Oracle , use something else as identifier. Also correct the ON syntax , a JOIN can have only one ON clause

update (
select d.t0100 as oldVal ,
       t.t2400 as newVaL
    from report.non_dst_readings d
        inner join report.dst_readings t
            on d.cust = t.cust
            WHERE to_date(d.readdate_ndst, 'dd-mm-yyyy') 
                 = (to_date(t.readdate, 'dd-mm-yyyy') -1)
        )
 set oldVal = newVaL;