Re: sql update statement help required

mrique_at_hotmail.com (mrique) wrote in message news:<a3fccd34.0207091418.cbfa63c_at_posting.google.com>...
> 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

update problem a set a.date_stamp=sysdate where exists (select 1 from "file" b where b.key=a.file_key), followed by, insert into problem (file_key,date_stamp) select key,sysdate from "file" a where not exists (select 1 from problem b where b.file_key=a.key)

You can also do this using PL/SQL. In Oracle9i you can do both update and insert in a single MERGE statement. Received on Tue Jul 09 2002 - 21:25:02 CDT