Re: sql update statement help required

mrique wrote:

> hi,
>
> newbie to oracle sql need to do this job :
>
> I have a table "file" (file.key, file.price) that I need to scan every
> day to detect quality problem (example : no price recorded) and record
> it with a time stamp, in a table "problem" (problem.file_key,
> problem.date_stamp)
>
> the statement I'm trying to do will do this job :
>
> Select from table file where the field "file.price" is empty
> IF file.price is null, THEN
> - if file.key has allready been recorded in table "problem" (field
> problem.file_key) with an old date, then update the date for this row
> - if file.key is not found in table "problem", then insert file.key,
> sysdate in problem.file_key, problem.date_stamp
> END IF
> Really don't know how to do that, Hope seniors could give 2 minutes to
> help and teach !
> PS : I'm using ODBC on an MS .asp work
> Thanks

First thing you need to do is rename your table. The word "FILE" is a reseved word. To find a list of words you should never use to name anything you do in Oracle run the following:

SELECT keyword
FROM v_$reserved_words;

Will this do what you want?

Realistically what you need here is a small stored procedure with a cursor loop. Open the cursor on the file table (after you rename it) and put a WHERE clause on the cursor so that it only looks at records where the price is NULL.

The inside the loop, update the record and perform the insert.

Daniel Morgan Received on Tue Jul 09 2002 - 17:40:10 CDT