ORA-00933: SQL command not properly ended-- What am I missing?

Question

Pretty simple update... or so I thought. I am receiving error: ORA-00933: SQL command not properly ended for the below query.

What am I missing?

Running Oracle 12c

Query:

UPDATE P
SET    P.SERVICE_DATE_OUT = NULL,
       P.SERVICE_DATE_IN = NULL 
FROM   PMEQMT P 
       JOIN ARINVT A ON A.ITEMNO = P.EQNO
       JOIN FGMULTI F ON F.ARINVT_ID = A.ID
WHERE  P.SERVICE_DATE_OUT IS NOT NULL AND
       P.SERVICE_DATE_IN IS NOT NULL AND
       P.CLASS = 'TL' AND
       F.LOC_ID = 27354

score 2 · Answer 1 · answered Apr 03 '19 at 20:48

You could rewrite it using inline view:

UPDATE (
  SELECT P.SERVICE_DATE_OUT, P.SERVICE_DATE_IN
  FROM   PMEQMT P 
       JOIN ARINVT A ON A.ITEMNO = P.EQNO
       JOIN FGMULTI F ON F.ARINVT_ID = A.ID
  WHERE  P.SERVICE_DATE_OUT IS NOT NULL AND
       P.SERVICE_DATE_IN IS NOT NULL AND
       P.CLASS = 'TL' AND
       F.LOC_ID = 27354
) s
SET s.SERVICE_DATE_OUT = NULL,
    s.SERVICE_DATE_IN = NULL;

Oracle does not support UPDATE FROM JOIN syntax.

Littlefoot · Answer 2 · 2019-04-03T21:09:11.843

1

Alternatively (to Lukasz's code), see whether something like this helps:

[EDIT]

You said that query returns the same error. Well, it does not - not for me.

I've created sample tables (just necessary columns), ran the query and - it is correct as far as syntax is concerned.

SQL> create table pmeqmt (service_date_out date, service_date_in date, eqno number, class varchar2(2));

Table created.

SQL> create table arinvt (id number, itemno number);

Table created.

SQL> create table fgmulti (arinvt_id number, loc_id number);

Table created.

SQL>
SQL> update pmeqmt p set
  2    p.service_date_out = null,
  3    p.service_date_in = null
  4  where p.eqno in (select a.itemno
  5                   from arinvt a join fgmulti f on f.arinvt_id = a.id
  6                   where f.loc_id = 27354
  7                  )
  8    and p.service_date_out is not null
  9    and p.service_date_in is not null
 10    and p.class = 'TL';

0 rows updated.

SQL>

edited Apr 03 '19 at 21:09

answered Apr 03 '19 at 20:49

Littlefoot

131,892
15
35
57

Well that is a bummer. This one is giving me the same error as well. – Jeff Guttry Apr 03 '19 at 21:04
Well, it shouldn't. I edited my message by adding a *working example*; have a look, please. If your attempt still return ORA-00933, please, do as I did: edit your initial message, copy/paste your SQL*Plus session (don't add that as a comment, it won't be readable). – Littlefoot Apr 03 '19 at 21:10
This worked! I must have grabbed the code from above yours. Thank you for providing a solution! – Jeff Guttry Apr 03 '19 at 21:32

ORA-00933: SQL command not properly ended-- What am I missing?

2 Answers2