replacing TSQL NOT EXISTS in SQL-92

Question

I am have following code which works well in TSQL:

BEGIN 
IF NOT EXISTS (select * from tblDCUSTOM where id = 'All Customers')
    BEGIN
    INSERT INTO tblDCUSTOM
        (ID
    ,Name
    ,English     
   )
   SELECT 'All Customers','All Customers','All Customers'
   END
END

Now, I need to have this functionality in an custom environment, where SQL-92 is used - so no EXISTS (edit: not true, EXISTS works in SQL-92) or BEGIN-END is possible. Any Ideas?

Put the condition in the `WHERE`: `INSERT ... SELECT ... WHERE NOT EXISTS (...)`. This is arguably better practice even in T-SQL, to make the operation atomic. — Jeroen Mostert, Oct 24 '22 at 08:27
@JeroenMostert Thanks, but this is not really an answer as the 'NOT EXISTS' is not an option in SQL-92 — Lukáš Tomšů, Oct 24 '22 at 08:33
Can you use an `IN`? `WHERE 'All Customers' NOT IN (SELECT id FROM tblDCUSTOM)`? — Thom A, Oct 24 '22 at 08:35
I'm looking at a draft of the ANSI 92 standard and `` is defined on page 222 of that. Where are you looking that suggests `EXISTS` isn't an option *at all*? — Damien_The_Unbeliever, Oct 24 '22 at 08:36
I have no copy of the ANSI SQL standard handy, but I must say I find that quite improbable. It's certainly possible your "custom environment" doesn't support it, because almost no environment implements the standard fully, or exactly, so it may simply be lying when it claims adherence. `IF` and `BEGIN` and `END` and other imperative constructs are not present in the standard (nor are they standardized across systems), but `EXISTS` should be. — Jeroen Mostert, Oct 24 '22 at 08:36

score 1 · Accepted Answer · answered Oct 24 '22 at 11:20

As per very first comment;

   INSERT INTO tblDCUSTOM
        (ID
    ,Name
    ,English     
   )
   SELECT 'All Customers','All Customers','All Customers'
   WHERE (SELECT COUNT(*) FROM tblDCUSTOM where id = 'All Customers') >= 1

If TOP is supported this might be better

   INSERT INTO tblDCUSTOM
        (ID
    ,Name
    ,English     
   )
   SELECT 'All Customers','All Customers','All Customers'
   WHERE (SELECT TOP 1 1 as F FROM tblDCUSTOM where id = 'All Customers') IS NOT NULL

I must warn you, many have tried to make a 'database agnostic' system. It's not worth it.

score 0 · Answer 2 · edited Oct 27 '22 at 12:45

0

This is the correct answer, the EXISTS statement IS actually supported:

Put the condition in the WHERE: INSERT ... SELECT ... WHERE NOT EXISTS (...). This is arguably better practice even in T-SQL, to make the operation atomic.

edited Oct 27 '22 at 12:45

James Risner

5,451
11
25
47

answered Oct 24 '22 at 08:55

Lukáš Tomšů

69
4

replacing TSQL NOT EXISTS in SQL-92

2 Answers2