SQL UPDATE - INNER JOIN QUERY

Question

I am trying to do an update in 2 tables, but i have this error:

Error SQL: ORA-00933: "SQL command not properly ended".

Could you help me please? The query is:

UPDATE a
SET a.ACTORID_ = SUBSTR(a.ACTORID_, 2, LENGTH(a.ACTORID_)),
    b.TASKACTORID_ = SUBSTR(b.TASKACTORID_, 2, LENGTH(b.TASKACTORID_))
FROM jbpm_taskinstance AS a
    INNER JOIN jbpm_log AS b 
    ON b.TASKACTORID_ = a.ACTORID_
WHERE a.ACTORID_ = b.TASKACTORID_
     AND b.TASKINSTANCE_ IN (
          SELECT ID_ 
          FROM jbpm_taskinstance 
          WHERE a.PROCINST_ =b.PROCINST_)
     AND b.TASKACTORID_ = a.ACTORID_;

@a_horse_with_no_name I think questions that are shown in duplicate and this question seems different. OP wants to update two different tables in a single statement and the duplicate questions are updating one table using another table. If you agree then you can re-open it. — Popeye, Apr 20 '20 at 06:50
I have read all related questions, but i dont know where is the error. — Amaia Espinosa, Apr 20 '20 at 07:05
You can't use FROM or JOIN in an UPDATE statement and you can't update more than one table. — , Apr 20 '20 at 07:13
I have voted to re-open it. Let's see another answerers vote to re-open it or not!! — Popeye, Apr 20 '20 at 07:56
@AmaiaEspinosa . . . I would instead suggest that you delete this question. Ask a *new* question with an explanation of what you want to accomplish along with sample data and desired results. Don't make other people have to re-engineer what you want from broken SQL. — Gordon Linoff, Apr 20 '20 at 12:00

score 1 · Answer 1 · answered Apr 20 '20 at 08:15

Welcome to the world of strange and misleading Oracle error messages!

With experience, you can spot the error by sight, as @a_horse_with_no_name has done.

If you don't see the error immediately, I'd recommend to make the query simpler step by step until the error disappears. In your case, I would remove the AND b.taskinstance_ IN () subquery and check if the same error comes up. Then I'd remove the SUBSTR with a simple constant, like SET a.ACTORID_ = 'a'. Then I'd remove the JOIN, updating only table A. This will run ok, so you need to read up Oracle's documentation on UPDATE.

SQL UPDATE - INNER JOIN QUERY

1 Answers1