BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kcskaiser
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

4 REPLIES 4
billfish
Quartz | Level 8

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
Fluorite | Level 6

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
Opal | Level 21

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

PG
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 26017 views
  • 3 likes
  • 4 in conversation