Can I make an update using also a FROM statement?

Question

I would like to make an UPDATE on some fields of a table including also a FROM condition like in the following:

UPDATE T_NEW_TABLE_IMPORT_STATUS TB
SET
    ID = NULL,
    IMPORT_DATE = NULL,
    IMPORT_STATUS = 0,
    ERROR_CODE = 3,
    LASTMODIFIEDAT = NULL
FROM T_BEDA
WHERE 1=1
    AND TB.TRANSACTION_ID = 999
    AND TB.TRANSACTION_ID = T_BEDA.TAC
    AND T_BEDA.SOP IS NULL;

In the current status it fails with the following error:

Error report -
SQL Error: ORA-00933: SQL command not properly ended

Apparently the FROM clause is not allowed in this case.

Basically, I would like this update to take place only when the T_BEDA.SOP is NULL. How can I solve this using SQL or PL/SQL?

See [Oracle SQL: Update a table with data from another table](https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) — MBijen, May 18 '18 at 08:48
Possible duplicate of [Oracle SQL: Update a table with data from another table](https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) — MBijen, May 18 '18 at 08:49
You have to join T_BEDA & T_NEW_TABLE_IMPORT_STATUS in the FROM clause — Venkataraman R, May 18 '18 at 08:50
@VenkataramanR No, Oracle does not support such update join syntax. Well, maybe, if you count updating an inline view, but you never said that. — Tim Biegeleisen, May 18 '18 at 08:55

Ludovic Feltz · Accepted Answer · 2018-05-18T08:56:27.600

2

Use IN clause to check if the TRANSACTION_ID exists in T_BEDA:

UPDATE T_NEW_TABLE_IMPORT_STATUS TB
SET ID = NULL,
    IMPORT_DATE = NULL,
    IMPORT_STATUS = 0,
    ERROR_CODE = 3,
    LASTMODIFIEDAT = NULL
WHERE TB.TRANSACTION_ID = 999 
  AND TB.TRANSACTION_ID IN (
        SELECT T_BEDA.TAC 
        FROM T_BEDA 
        WHERE TB.TRANSACTION_ID = T_BEDA.TAC
          AND T_BEDA.SOP IS NULL
    )

edited May 18 '18 at 08:56

answered May 18 '18 at 08:50

Ludovic Feltz

11,416
4
47
63

score 2 · Answer 2 · answered May 18 '18 at 08:52

It looks like you are trying to use Postgres update syntax. Oracle does not support this, nor does it support update joins, but you may rephrase your update to use a subquery in the WHERE clause to inspect the second table:

UPDATE T_NEW_TABLE_IMPORT_STATUS TB
SET ID = NULL,
    IMPORT_DATE = NULL,
    IMPORT_STATUS = 0,
    ERROR_CODE = 3,
    LASTMODIFIEDAT = NULL
WHERE
    TB.TRANSACTION_ID = 999 AND
    EXISTS (SELECT 1 FROM T_BEDA t WHERE TB.TRANSACTION_ID = t.TAC AND t.SOP IS NULL)

score 0 · Answer 3 · edited May 18 '18 at 09:24

0

Here is what i would do:

UPDATE T_NEW_TABLE_IMPORT_STATUS TB
 SET ID = NULL,IMPORT_DATE = NULL,IMPORT_STATUS = 0,ERROR_CODE = 3, LASTMODIFIED = NULL 
WHERE 
 (TB.TRANSACTION_ID = 999) AND 
 (TB.TRANSACTION_ID) = (Select T_BEDA.TAC From T_BEDA WHERE T_BEDA.SOP           IS NULL)

edited May 18 '18 at 09:24

Ludovic Feltz

11,416
4
47
63

answered May 18 '18 at 09:11

Ted

30
2

Can I make an update using also a FROM statement?

3 Answers3