I am updating table's column values with UPDATE and SELECT statement. But its not working

I am updating a PREVIOUS_DAY_CLOSE column which contains DAY_CLOSE-1 values.

I am using the LAG() function to get PREVIOUS_DAY_CLOSE values. It is working fine.

But updating the columns with UPDATE and SELECT statements which is showing errors.

I have tried the queries shown as below, and I am getting these errors:

WITH t1 AS
(
    SELECT 
        datetime, 
        LAG(CLOSE) OVER (ORDER BY datetime) AS PREVIOUS_CLOSE 
    FROM
        stocks 
    WHERE 
        SYMBOL = 'NASDAQ:IXIC'
)
UPDATE STOCKS
SET PREVIOUS_CLOSE = t1.PREVIOUS_CLOSE
FROM t1
WHERE t1.datetime =STOCKS.datetime;

I got this error:

ORA-00928: missing SELECT keyword

UPDATE STOCKS
SET PREVIOUS_CLOSE = (SELECT LAG(CLOSE) OVER (ORDER BY datetime) AS PREVIOUS_CLOSE 
                      FROM STOCKS  
                      WHERE SYMBOL = 'NASDAQ:IXIC');

I got this error:

SQL Error: ORA-01427: single-row subquery returns more than one row

You can't use a CTE directly for an update, but you can as part of a select within the update. Either way you need to correlate the update, which you aren't quite doing as you are only basing that on datetime.

As mentioned on you previous question you may only want the previous value for display purposes, which you can get with a simple lag() query:

select symbol, datetime, open, close,
  lag(close) over (partition by symbol order by datetime) as previous_close
from stocks

But as you want to update the table directly, you can use that as a subquery, either in an update with a query against an inline view:

update stocks s
set previous_close = (
  select previous_close
  from (
    select symbol, datetime,
      lag(close) over (partition by symbol order by datetime) as previous_close
    from stocks
  ) t
  where t.symbol = s.symbol and t.datetime = s.datetime
)

or the same thing but with a CTE instead of an inline view:

update stocks s
set previous_close = (
  with t (symbol, datetime, previous_close) as (
    select symbol, datetime,
      lag(close) over (partition by symbol order by datetime)
    from stocks
  )
  select previous_close
  from t
  where t.symbol = s.symbol and t.datetime = s.datetime
)

Or you can use a merge instead of an update:

merge into stocks s
using (
  select symbol, datetime,
    lag(close) over (partition by symbol order by datetime) as previous_close
  from stocks
) t
on (t.symbol = s.symbol and t.datetime = s.datetime)
when matched then update set s.previous_close = t.previous_close;

All three have the same result;

select * from stocks order by symbol, datetime desc;

fiddle