Oracle SQL What is the right syntax to Update a table from a join in a trigger?

Question

I have 3 tables :

portees : represent an electricity line with several columns (por_cur, equipment A, equipment B, ..., iqu_cur, examine)

cab_portees : represent a specific equipment on the lines (there can be many instance of those equipment on one line), Columns (id, por_cur (foreign key from portees), ..., iqu_cur)

indice_qualite : represent 1 attribut of one of the other two table which is considered important data. Columns (table_name, attribut_name, ...,iqu_cur, examine)

We check the data in table portees and cab_portees to be sure its correct, when one line is checked we update portees.examine to '1'. I want to make a trigger to auto update the table indice_qualite : put all the equipment from the table to examine = '1'.

I have trouble getting the right syntax, here is what I have :

    create or replace trigger PORTEES_EXAMINE_TRIGGER
    AFTER UPDATE ON PORTEES
    FOR EACH ROW

    BEGIN

    if :new.EXAMINE != :old.EXAMINE then

   UPDATE (SELECT INDICE_QUALITE.EXAMINE FROM 
          INDICE_QUALITE, PORTEES
          WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
          AND PORTEES.IQU_CUR = :old.IQU_CUR
          UNION
          SELECT INDICE_QUALITE.EXAMINE FROM 
          INDICE_QUALITE, CAB_PORTEES
          WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
          AND CAB_PORTEES.POR_CUR = :old.POR_CUR)

    SET INDICE_QUALITE.EXAMINE = :new.EXAMINE;

    end if;

    END PORTEES_EXAMINE_TRIGGER;

Any ideas ?

You can't write update query with join. You need to use merge — swaroop pallapothu, Oct 12 '17 at 09:22
Which table are you updating? You can use rules in a where clause of the update statement (possibly using exists), so you should just rewrite query that selects rows for updating. Or use merge. — Goran Stefanović, Oct 12 '17 at 09:34
I am updating the table indice_qualite, I will have a look at merge. — Tr4d, Oct 12 '17 at 10:57
BTW. ORA-00904 means that identifier is invalid, not that table does not exist! You are obviously trying to select TABLE in a select statement, not column. — Goran Stefanović, Oct 12 '17 at 11:02

swaroop pallapothu · Accepted Answer · 2017-10-12T11:10:13.230

0

You cant write join with update query. Use Merge to do join.

MERGE INTO INDICE_QUALITE M USING
  (SELECT EXAMINE,
   INDICE_QUALITE.IQU_CUR
   FROM INDICE_QUALITE,
        PORTEES
   WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
     AND PORTEES.IQU_CUR = :old.IQU_CUR
   UNION 
   SELECT EXAMINE,
   INDICE_QUALITE.IQU_CUR
   FROM INDICE_QUALITE,
        CAB_PORTEES
   WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
     AND CAB_PORTEES.POR_CUR = :old.POR_CUR) T ON (M.IQU_CUR = T.IQU_CUR) WHEN MATCHED THEN
UPDATE
SET EXAMINE = :new.EXAMINE;

edited Oct 12 '17 at 11:10

answered Oct 12 '17 at 09:26

swaroop pallapothu

588
1
6
15

Hi, thanks for answering, I tried your suggestion and had the same error. – Tr4d Oct 12 '17 at 09:37
Can you paste exact error – swaroop pallapothu Oct 12 '17 at 09:38
PL/SQL: ORA-00904: "INDICE_QUALITE" : not valid identificator – Tr4d Oct 12 '17 at 10:56
Do you have INDICE_QUALITE column in INDICE_QUALITE table? – swaroop pallapothu Oct 12 '17 at 11:00
No I dont, the column I wish to update is EXAMINE from INDICE_QUALITE table. – Tr4d Oct 12 '17 at 11:05
query updated check now – swaroop pallapothu Oct 12 '17 at 11:10
@swaroop pallapothu: You do not need INDICE_QUALITE table in the USING clause at all, you are already comparing IQU_CUR in the ON clause. Also, you do not need EXAMINE in the using clause, this may lead to more rows being returned if EXAMINE is different for the same IQU_CUR in PORTEES and CAB_PORTEES tables (same row may be updated twice). Otherwise this is good answer. – Goran Stefanović Oct 12 '17 at 11:22

Wernfried Domscheit · Answer 2 · 2017-10-12T11:12:07.930

Try this one:

UPDATE (SELECT INDICE_QUALITE.EXAMINE  FROM 
      INDICE_QUALITE, PORTEES
      WHERE PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      AND PORTEES.IQU_CUR = :old.IQU_CUR
      UNION ALL
      SELECT INDICE_QUALITE.EXAMINE FROM 
      INDICE_QUALITE, CAB_PORTEES
      WHERE CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      AND CAB_PORTEES.POR_CUR = :old.POR_CUR) t
SET t.EXAMINE = :new.EXAMINE;

However, I don't know if this works with UNION ALL (for sure it does not work with UNION). Also you should prefer ANSI join syntax instead of old Oracle join syntax.

Otherwise try:

UPDATE (SELECT INDICE_QUALITE.EXAMINE  
        FROM INDICE_QUALITE
           JOIN CAB_PORTEES ON CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      WHERE PORTEES.IQU_CUR = :old.IQU_CUR) t
SET t.EXAMINE = :new.EXAMINE;

UPDATE (SELECT INDICE_QUALITE.EXAMINE  
        FROM INDICE_QUALITE
           JOIN PORTEES ON CAB_PORTEES.IQU_CUR = INDICE_QUALITE.IQU_CUR
      WHERE CAB_PORTEES.POR_CUR = :old.POR_CUR) t
SET t.EXAMINE = :new.EXAMINE;

Note, such update works only when you have UNIQUE index/constraint on CAB_PORTEES.IQU_CUR and INDICE_QUALITE.IQU_CUR

I tried first suggestion, I get this error :PL/SQL: ORA-00918: Ambigus colomn definition, for the second suggestion (you made an error on second join it's JOIN CAB_PORTEES) I get this error : PL/SQL: ORA-00904: "INDICE_QUALITE" : not valid identificator — Tr4d, Oct 12 '17 at 11:03
Please try now, your initial statement has several errors. It is not so easy to guess your intention without any explanation. — Wernfried Domscheit, Oct 12 '17 at 11:15
@Wernfried Domscheit: I just tried query with UNION ALL, it does not work - I get ORA-01732 regardless if UNION or UNION ALL is used. This is on 11.2.0.3. — Goran Stefanović, Oct 12 '17 at 11:28

Goran Stefanović · Answer 3 · 2017-10-12T11:19:03.817

You can rewrite the query this way:

UPDATE INDICE_QUALITE iq
   SET iq.EXAMINE = :new.EXAMINE
 WHERE EXISTS (SELECT 1
                 FROM PORTEES p
                WHERE p.IQU_CUR = iq.IQU_CUR
                  AND p.IQU_CUR = :old.IQU_CUR
                UNION ALL
               SELECT 1
                 FROM CAB_PORTEES cp
                WHERE cp.IQU_CUR = iq.IQU_CUR
                  AND cp.POR_CUR = :old.POR_CUR);

I've put all the rules in the exists condition of the update statement. I am not sure if this will work since the requirement is not clear - you did not explain anything, you've just provided non-working query.

Oracle SQL What is the right syntax to Update a table from a join in a trigger?

3 Answers3