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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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