Update query

[selavarti]

I am trying to update some fields in a table. But the condition for the update includes fields from other tables. Can some one please tell me what is wrong with this query. It says [1]: (Error): ORA-00933: SQL command not properly ended

update ctrn
set ctrn_cldt_clnt_id = 3002, ctrn_apply_code = 'O', ctrn_upadte_user= 'ISR3075'
from ctrn, audit_exceptions, audit_types
where ctrn_cldt_clnt_id = 3014
and ctrn_apply_code = 'H'
and ctrn_trnt_code = 'C'
and audx_audt_code = 7131
and ctrn_sid = AUDX_FOREIGN_KEY1
and AUDX_TABLE_ALIAS = 'CTRN'
and audx_audt_code = audt_code;

Thank you

 

[carp]

The syntax is wrong.
The syntax for an update is
UPDATE table_name
SET column_name = some_value
WHERE some_condition_exists;

If you are referencing data in other tables to determine which rows to update, then you will probably need a subquery in your WHERE clause:

UPDATE my_table
SET column1 = 5
WHERE column7 = (SELECT column2
FROM my_other_table
WHERE column4 = 'Hello');

 

[SantaMufasa]

Selavarti,

Oracle UPDATEs don't allow your "...from..." syntax in that form. Here is alternate code that slightly adjusts your syntax and should work for you:

update ctrn 
    set ctrn_cldt_clnt_id = 3002
      , ctrn_apply_code = 'O'
      , ctrn_upadte_user= 'ISR3075'
    where exists (select 'x' 
    from ctrn, audit_exceptions, audit_types
    where ctrn_cldt_clnt_id = 3014 
        and ctrn_apply_code = 'H' 
        and ctrn_trnt_code = 'C'
        and audx_audt_code = 7131
        and ctrn_sid = AUDX_FOREIGN_KEY1 
        and AUDX_TABLE_ALIAS = 'CTRN' 
        and audx_audt_code = audt_code);

Let us know if this works satisfactorily for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[selavarti]

Thank you guys for the correction.

Mufasa..the code seems to be correct..but it's in processing state for ever...I have to see why.

 

[SantaMufasa]

Selvarti,

To ensure that locks are not the culprit for the unacceptably long processing time, issue this command:

SELECT COUNT(*)
from ctrn, audit_exceptions, audit_types
    where ctrn_cldt_clnt_id = 3014 
        and ctrn_apply_code = 'H' 
        and ctrn_trnt_code = 'C'
        and audx_audt_code = 7131
        and ctrn_sid = AUDX_FOREIGN_KEY1 
        and AUDX_TABLE_ALIAS = 'CTRN' 
        and audx_audt_code = audt_code

The next thing that I would check is for indexes: ensure that indexes exist on the columns that your WHERE clause references.

Let us know if any of this produces results that are more acceptable.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.