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
 
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.

Comments