Re: IF - exists -SQL syntax?

On Fri, 16 Jun 2006 10:26:17 +0000, Major wrote:

> I tried somethin like this (based on SQL-server scirpt):
>
> IF NOT EXISTS (select * from table_a where field_a='xxx' ) THEN
> INSERT INTO table_a.... ;
> END IF;
>
> What is a right way to do check that the row is not in the table?

You may want to study the MERGE statement (which does an insert if not exists, update if exists)

You may want to use the power of Oracle's constraints capability and simply let the statement fail on duplicate.

If possible, avoid using process blocks - always second choice in Oracle ... use a a pure SQL statement whenever possible.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.