Conditional Insert

[ed_ardzinski]

I can easily write an insert statement to add a record to a
specific table. The table has an ID column...but I'd like to
use the EXISTS keyword to ignore the insert statment if a
record exists already with the same name. Any suggestions
would be appreciated.

[Paul Horan[TeamSybase]]

INSERT into table
(column list)
Select (matching select list)
where NOT EXISTS
(Select 1 from table T2
where T2.ID = table.ID ) ;

--
Paul Horan[TeamSybase]

Get the new PB9 books!
http://www.pb9books.com?source=newsgroups

Code samples on Sybase CodeXchange:
http://codexchange.sybase.com

ISUG Enhancement Requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

<Ed Ardzinski> wrote in message news:3f4e60c6.61...@sybase.com...

[ed_ardzinski]

Thanks Paul...but I don't want to select from another table,
I want to use a values list and condition the insert on the
existence of the record in the same table...

This is what I'm trying...although I get a syntax error mear
where I try to use the where clause. I started yesterday
trying to use an If/Then structure:

insert into
other_referral (other_referral_id, name, status, last_name,
source_or_recipient, specialty)
values ((select max(other_referral_id)+1 from
other_referral), '1-800-DENTIST', 'A', '1-800-DENTIST', 'S',
-1)
where not exists (select * from other_referral where name =
'1-800-DENTIST')

[Reg Domaratzki]

What version of ASA are you using? The ON EXISTING clause extension to the
INSERT statement sounds like it will do exactly what you want. This was
added in v8.

INSERT INTO t1
ON EXISTING SKIP
VALUES (1,'new_value');

From the docs :

If you specify the ON EXISTING clause, the server does a primary key lookup
for each input row. If the corresponding row does not already exist in the
table, it inserts the new row as usual. For rows that already exist in the
table, you can choose to silently ignore the input row (SKIP), update the
values in the input row (UPDATE), or generate an error message for duplicate
key values (ERROR).

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Ed Ardzinski> wrote in message news:3f4e60c6.61...@sybase.com...

[Paul Horan[TeamSybase]]

Is this within a proc/function? You could do it like this:

If not exists (
select 1 from other_referral
where name = '1-800-DENTIST' ) then

insert into other_referral (
other_referral_id,

name,
status,
last_name,
source_or_recipient,
specialty)

(select
max(other_referral_id)+1,

'1-800-DENTIST',
'A',
'1-800-DENTIST',
'S',
-1

from other_referral )

End if;

--
Paul Horan[TeamSybase]

Get the new PB9 books!
http://www.pb9books.com?source=newsgroups

Code samples on Sybase CodeXchange:
http://codexchange.sybase.com

ISUG Enhancement Requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement

<Ed Ardzinski> wrote in message news:3f4f4609.f4...@sybase.com...

[ed_ardzinski]

Thanks Paul, that did it.

BTW, Reg...using ASA 7. Sorry forgot to mention that.