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?
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
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.
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?
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
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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.