can we do self join in delete operation to delete one of duplicate records in vertica.
My requirement is to delete one duplicate records in one condition.
I am pulling the duplicate records with self join of a table and now wanna to delete after manual confirmation.
Can i use the same query for delete operation with self join.
the below is the query for select statement. how i can convert the same in delete in vertica.
SELECT a.stateid,a.SEVERITYID,a.Alarm_Text,a.LASTMODIFICATIONTIMESTAMP,a.ORIGINALEVENTTIME||'_'||a.logic_id,count(a.ORIGINALEVENTTIME||'_'||a.logic_id),a.IndusSiteID,a.AlarmName,a.circlename FROM
SCHEMA.TABLE A INNER JOIN SCHEMA.TABLE B ON A.alarm_text = B.alarm_text AND A.ORIGINALEVENTTIME = B.ORIGINALEVENTTIME AND A.logic_id = B.logic_id AND A.IndusSiteID = B.IndusSiteID
AND A.stateid = B.stateid AND B.stateid in (0,1,3) and a.circlename ilike 'Delh%' and a.additionaltext not ilike '%RESYNC FM%' group by
a.alARM_TEXT,a.ORIGINALEVENTTIME,a.logic_id,a.IndusSiteID,a.stateid,a.SEVERITYID,a.alarmname,a.circlename,a.LASTMODIFICATIONTIMESTAMP having count(a.ORIGINALEVENTTIME||'_'||a.logic_id)> 1 and
count(a.IndusSiteID)>1 and count(a.SEVERITYID)>1 and count(a.ALARM_TEXT)>1 and count(a.stateid)>1;
Comments
Can someone please suggest on this...........little urgent need to know as i am not able to do delete
Delete A.* for the query is not working as it works in sql oracle or other.