Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.