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