Using Proc SQL to delete rows with a where and having clause

[kcskaiser]

What I'd like to work is this:

proc sql;

     delete from odbclib.cp_tariff_dim

       where tariff_initial = 'NS'

           and tariff_item = '0010100000;

       having min(tariff_dim_key) ne tariff_dim_key

; quit;

This code works perfectly to create a data set as in select * from etc.

It creates a data set of all the rows I want to delete. But if I run the above I get an "ERROR 22-322: Syntax error...".

Is there a way to use a 'having' clause with the where in order to delete rows from a SQL Server table?

[billfish]

A point of view.

data t_a;
input a1 a2;
1 10
1 11
1 12
1 13
2 11
2 15
2 17
;

proc sql;
  delete from t_a a
  where a.a1 in (select a1 from
                (select b.a1, min(a2) as min_a2
                 from t_a b
                 group by b.a1
                 having min(a2)=10));
quit;

I end up with t_a becoming:

=============
a1    a2

2    11
2    15
2    17
=============

But SAS gives a warning:

WARNING: This DELETE/INSERT statement recursively references the target table. A consequence of this is a possible data integrity
         problem.

[kcskaiser]

Hmmm, very interesting approach. After adding a 'datalines' on your input I was able to reproduce your output. Additionally your solution isn't working correctly anyway because you should end up with just the first row (min(a2)=10), not three or four (min(a2) ge 10).  I get the same warning and like you it concerns me.

My table is a bit more complex so my actual code would need to look something like this:

proc sql;

  delete from cp_tariff_dim_backup a

  where a.TARIFF_INITIAL = 'NS'

   and a.TARIFF_ITEM = '0010100000'

   and a.TARIFF_NBR = '00025'

   and a.SRC_SYS = 'KCS'

   and a.TARIFF_ISS_ID = 'NSPQ'

  in (select tariff_dim_key from

  (select b.tariff_dim_key, min(tariff_dim_key) as min_dim

  from cp_tariff_dim_backup b

  group by b.tariff_dim_key

  having min(a.tariff_dim_key) <> b.tariff_dim_key))

  ;

quit;

Which gives me an 'ERROR 22-322: Syntax error..'.

Oddly enough (at least I find it odd) if you surround the condition checks in the where clause with parens () the syntax error goes away but no rows are deleted.

where (a.TARIFF_INITIAL = 'NS'

   and a.TARIFF_ITEM = '0010100000'

   and a.TARIFF_NBR = '00025'

   and a.SRC_SYS = 'KCS'

   and a.TARIFF_ISS_ID = 'NSPQ')

Thoughts?

[PGStats · Accepted solution]

The SQL code works for SELECT and not for DELETE because delete operations modify the set in which aggregate operations occur, and thus, their result.

What if min(tariff_dim_key) is not the same after you do the first deletion? Should it be evaluated again? Different answers to this question would lead to different results. SQL standard designers have probably chosen the safest approach to prevent inconsistencies between DBMSs.

PG

[jakarman]

You have a logical problem ACID - Wikipedia, the free encyclopedia as deleting records that could add to the value of the aggregated function.

That is why you are getting:

WARNING: This DELETE/INSERT statement recursively references the target table. A consequence of this is a possible data integrity   problem.