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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file