SQL, update command not ending properly

Question

It keeps saying : ORA-00933: SQL command not properly ended

Pls help me or give me a link to a solution

UPDATE emprunts
SET etat = 'RE'
FROM emprunts A
JOIN detailsemprunts B
    ON A.numero = B.emprunt
WHERE B.rendule is not null;```

Oracle doesn't support `FROM` in an `UPDATE`, so the whole query is wrong. — Gordon Linoff, Apr 30 '20 at 14:13
You have edited your question in response to the answers that were given. Please don't make significant changes to your question after it has been answered because now the answers that were given don't entirely make sense. — skomisa, Apr 30 '20 at 19:12

score 0 · Accepted Answer · answered Apr 30 '20 at 14:14

0

You can use a correlated subquery instead:

update emprunts e
set etat = 'etat'
where exists (
    select 1 
    from detailsemprunts de 
    where e.numero = de.emprunts and de.rendule is not null
)

answered Apr 30 '20 at 14:14

GMB

216,147
25
84
135

Thank you I've tried your code but now it says : ORA-12899: value too large for column "SQL_DUGAMOFGNGUZZGCQDYVJTDYMS"."EMPRUNTS"."ETAT" (actual: 4, maximum: 2) ORA-06512: at "SYS.DBMS_SQL", line 1721 – Elias Uchiwa Apr 30 '20 at 14:22
@EliasUchiwa: so don't write `'etat'`, write something else: `update emprunts e set etat = 'ab' where exists ....` – GMB Apr 30 '20 at 14:24
Yeah i forgot i've made a constraint in this table so this column can only be RE or EC, dunno why I've put 'etat' there. Well now it's working but it doesn't seem to be taking care of the 'de.rendule is not null' and still put RE on all the emprunt.etat column – Elias Uchiwa Apr 30 '20 at 14:29
Nevermind it's working like a charm now, thank's a lot ! – Elias Uchiwa Apr 30 '20 at 14:38

Gordon Linoff · Answer 2 · 2020-04-30T14:24:33.613

0

Oracle does not support FROM in UPDATEs. Although you could do this in a MERGE, I think an UPDATE with EXISTS is much more sensible:

UPDATE emprunts e
    SET etat = 'etat'
WHERE EXISTS (SELECT 1
              FROM detailsemprunts de
              WHERE e.numero = de.emprunts AND de.rendule is not null
             );

edited Apr 30 '20 at 14:24

answered Apr 30 '20 at 14:14

Gordon Linoff

1,242,037
58
646
786

Thank you I've tried your code(and changed B.rendule to de.rendule because rendule is a column of detailsemprunts) but now it says : ORA-12899: value too large for column "SQL_DUGAMOFGNGUZZGCQDYVJTDYMS"."EMPRUNTS"."ETAT" (actual: 4, maximum: 2) ORA-06512: at "SYS.DBMS_SQL", line 1721 – Elias Uchiwa Apr 30 '20 at 14:21
@EliasUchiwa . . . Choose a 2-character value to assign to the column. Apparently `'etat'` is too long. – Gordon Linoff Apr 30 '20 at 14:25
Yeah i forgot i've made a constraint in this table so this column can only be RE or EC, dunno why I've put 'etat' there. Well now it's working but it doesn't seem to be taking care of the 'de.rendule is not null' and still put RE on all the emprunt.etat column (Sorry if I'm giving the same explanation as the other guy but you're both helping me the same way and you're both very helpfull) – Elias Uchiwa Apr 30 '20 at 14:29
Nevermind it's working like a charm now, thank's a lot ! (I wish I could give you both working solution, randomly choosed one of you) – Elias Uchiwa Apr 30 '20 at 14:38

SQL, update command not ending properly

2 Answers2