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

   
create table eakan (sec\_id int, nameid int, date date);  
insert into public.eakan values(1,1,'2013-05-20');  
insert into public.eakan values(2,1,'2013-05-20');  
insert into public.eakan values(3,1,'2013-05-20');  
insert into public.eakan values(1,1,'2013-05-21');  
insert into public.eakan values(2,1,'2013-05-21');  
insert into public.eakan values(3,1,'2013-05-21');  

```I also created another table using the records of which I have to delete records in the earlier one.```
   
create table eakan\_del (sec\_id int, nameid int, date date);  
insert into public.eakan\_del values(1,1,'2013-05-21');  
commit;  

```I was naive initially and did something like this:  
First try:```
   
DELETE from eakan   
 WHERE eakan.sec\_id IN   
 (select eakan\_del.sec\_id  
    from eakan\_del  
   where eakan\_del.sec\_id=eakan.sec\_id  
     and eakan\_del.date=eakan.date  
     and eakan\_del.nameid=eakan.nameid)  
   and eakan.date IN   
 (select eakan\_del.date  
 from eakan\_del  
   where eakan\_del.sec\_id=eakan.sec\_id  
     and eakan\_del.date=eakan.date  
  and eakan\_del.nameid=eakan.nameid)  
   and eakan.nameid in   
 (select eakan\_del.nameid   
    from eakan\_del   
   where eakan\_del.sec\_id=eakan.sec\_id  
     and eakan\_del.date=eakan.date   
  and eakan\_del.nameid=eakan.nameid);  

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);

And bingo! I nailed it!  
That 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.