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
Post a Comment