Use Update with INNER JOIN in Oracle

Question

Use Update with INNER JOIN in Oracle

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

How about,

I am running a scan in Oracle ( Update ) using INNER JOIN , but I could not do it. This is the code I am using:

UPDATE A
SET A.status = '2', target_date=SYSTIMESTAMP
FROM Table_A  A INNER JOIN TABLE_B B
   ON A.employee_number=B.employee_number and A.course_number=B.course_number
 WHERE A.status = '1' and NVL(B.employee_number,'')=''

The error that appears to me when executing the sentence is the following:

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

I really do not know what I'm doing wrong. Any suggestions to my problem?

Greetings

oracle

asked by Edgar Conrado 25.05.2016 в 22:19

source

2 answers

1

this part NVL (B.employee_number, '') = '' I'm curious, I do not know if maybe that gives you problems and if you try B.employee_number is null

answered by 25.05.2016 в 22:24

Return of a promise then () for each element of an array How to use a variable from one form of c # in another [closed]

score 2 · Accepted Answer

That UPDATE syntax with JOIN is more typical of SQL Server. In Oracle it is not supported.

I have not been able to prove it but it should be something like this:

UPDATE
(SELECT A.status, A.target_date
    FROM Table_A  A 
    INNER JOIN TABLE_B B
        ON A.employee_number=B.employee_number
            AND A.course_number=B.course_number
    WHERE A.status = '1' 
        AND NVL(B.employee_number,'')='') t
SET t.status = '2', t.target_date = SYSTIMESTAMP;

Another option:

UPDATE Table_A SET Table_A.status = '2', Table_A.target_date = SYSTIMESTAMP
WHERE Table_A.status = '1'
    AND COALESCE(Table_A.employee_number, '')=''
    AND EXISTS (SELECT Table_B.course_number
                FROM Table_B
                WHERE Table_B.course_number = Table_A.course_number
                    AND Table_B.employee_number = Table_A.employee_number);