Vertica DELETE columns from one table using data from another

My colleague had an issue in Vertica. He had a set of values in table 1 to be deleted based on the records in a table table2. I didn't think this would be easy considering VERTICA didn't support joins when doing deletes unlike MYSQL. But they do have something else. Let me take you through the steps I followed. I created the following table in which I have records

 1   
 2create table eakan (sec\_id int, nameid int, date date);  
 3insert into public.eakan values(1,1,'2013-05-20');  
 4insert into public.eakan values(2,1,'2013-05-20');  
 5insert into public.eakan values(3,1,'2013-05-20');  
 6insert into public.eakan values(1,1,'2013-05-21');  
 7insert into public.eakan values(2,1,'2013-05-21');  
 8insert into public.eakan values(3,1,'2013-05-21');  
 9
10```I also created another table using the records of which I have to delete records in the earlier one.```
11   
12create table eakan\_del (sec\_id int, nameid int, date date);  
13insert into public.eakan\_del values(1,1,'2013-05-21');  
14commit;  
15
16```I was naive initially and did something like this:  
17First try:```
18   
19DELETE from eakan   
20 WHERE eakan.sec\_id IN   
21 (select eakan\_del.sec\_id  
22    from eakan\_del  
23   where eakan\_del.sec\_id=eakan.sec\_id  
24     and eakan\_del.date=eakan.date  
25     and eakan\_del.nameid=eakan.nameid)  
26   and eakan.date IN   
27 (select eakan\_del.date  
28 from eakan\_del  
29   where eakan\_del.sec\_id=eakan.sec\_id  
30     and eakan\_del.date=eakan.date  
31  and eakan\_del.nameid=eakan.nameid)  
32   and eakan.nameid in   
33 (select eakan\_del.nameid   
34    from eakan\_del   
35   where eakan\_del.sec\_id=eakan.sec\_id  
36     and eakan\_del.date=eakan.date   
37  and eakan\_del.nameid=eakan.nameid);  
38

Then I realized there should be another way and used some documentation of Vertica and also checked out Vertica Forums if something was available.

And came up with this:```

delete from eakan
where exists
(select null
from eakan_del
where eakan_del.sec_id=eakan.sec_id
and eakan_del.date=eakan.date
and eakan_del.nameid=eakan.nameid);

1And bingo! I nailed it!  
2That implies that only those rows from eakan would be deleted which matches all the conditions given in the subquery selecting stuff from eakan\_del. I am not sure if this is normal SQL. This is probably Vertica only.
comments powered by Disqus