Help needed on updating field

[adnil]

i've got the following script & i kept getting this msg:
ERROR at line 3:
ORA-00933: SQL command not properly ended.

update sen_prov
set pe_date = 20/07/2006
from sen_prov
inner join stud on sen_prov.stud_id = stud.stud_id
inner join bases on sen_prov.base_id = bases.base_id
where stud_ncy = 2 and
bases.type_id = 'INF' and
sen_prov.pe_date = 31/03/2007;

can someone tell me where i've gone wrong pls?

cheers

 

[jimirvine]

to use date:

where datecolumn = to_date('datestring', 'datestringformat')
e.g.

where hire_date = to_date('10-oct-2001','dd-mon-yyyy')

Note, you may also have to take a time element into account as Oracle also stores hours minutes and seconds with a date datatype.+

 

[Dagon]

a) dates must be in quoted strings and ideally you should use a to_date function with a format model. This is because different systems can have different settings for the default date format.

b) there is no "from" clause with an update. You need to use a subquery to reference other tables.

Try this version:

update sen_prov
set pe_date = to_date('20/07/2006', 'DD/MM/YYYY')
where exists
(select 1 from stud, bases
where sen_prov.stud_id = stud.stud_id
and sen_prov.base_id = bases.base_id
and stud_ncy = 2
and bases.type_id = 'INF')
and sen_prov.pe_date = to_date('31/03/2007', 'DD/MM/YYYY')

 

[adnil]

thank you for your help.

it works like a charm.

cheers

 

[SantaMufasa]

Adnil,

Following such excellent assistance from other Tek-Tipsters, it is customary to click the "Thank Dagon for this valuable post!" and "Thank JimIrvine for this valuable post!" links to reward them with well-deserved Purple Stars.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]