CODEWITHSUNDEEP
sqloracle-database
Jack
Jack

Reputation: 9

Oracle: Update where error

Good morning community.

I have been using MSSQL for a while, and have not had this issue with MSSQL, so it seems to be a way Oracle handles sql. Basically, I need to update two columns in the Table DANIEL_ALERT, based on the where condition. However, I get the " ORA-00904: "ALERT_CATEORIES"."ALERT": invalid identifier. I am sure it is just the way oracle does things which is different to MSSQL.

Any ideas would be greatly appreciated. Thank you in advance :)

UPDATE DANIEL_ALERT
SET DANIEL_ALERT.ALERT_CATEGORY = ALERT_CATEGORIES.INDICATION,
DANIEL_ALERT.ALERT_THEME = ALERT_CATEGORIES.THEME
WHERE DANIEL_ALERT.ALERT_NAME = ALERT_CATEGORIES.ALERT;
commit;

Upvotes: 0

Views: 62

Answers (3)

gmiley
gmiley

Reputation: 6604

Not knowing what your table structures and content looks like, you could go with this approach, to go along with, or as an alternative to the perfectly fine MERGE answer already posted:

UPDATE DANIEL_ALERT DA
SET (DA.ALERT_CATEGORY, DA.ALERT_THEME) = (
    SELECT AC.INDICATION, AC.THEME 
    FROM ALERT_CATEGORIES AC 
    WHERE AC.ALERT = DA.ALERT_NAME
)
WHERE EXISTS (
    SELECT NULL
    FROM ALERT_CATEGORIES AC
    WHERE AC.ALERT = DA.ALERT_NAME
);

Upvotes: 1

user5683823
user5683823

Reputation:

In Oracle, updating a table with values from another table is often best done with the MERGE statement. Something like:

merge into DANIEL_ALERT d
  using ALERT_CATEGORIES c
     on (d.ALERT_NAME = c.ALERT)
when matched then update 
   set ALERT_CATEGORY = c.INDICATION,
       ALERT_THEME    = c.THEME
;

Upvotes: 0

Pr3ds
Pr3ds

Reputation: 358

try :

UPDATE DANIEL_ALERT SET t1.ALERT_CATEGORY = t2.INDICATION, t1.ALERT_THEME = t2.THEME FROM DANIEL_ALERT t1 INNER JOIN ALERT_CATEGORIES t2 ON t2.ALERT = t1.ALERT_NAME

Upvotes: 0

Related Questions