Deletes no proper counts

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Deletes no proper counts

Post by nnani » Thu Sep 19, 2013 6:19 pm

Hello All,

I want to achieve this.

I have a target table and a stage table
I want to delete data from target table that is present in the stage table .i.e. all the matching records in stage and target table based on a key should be deleted from the Target table


How are these two logic different

Code: Select all

Delete from target_table
where exists
(select null from stage_table
where stage_table.column_key=target_table.column_key);
Is this the right logic?

Code: Select all

I tried figuring it out what is number of matching records between the two tables
select count(column) from target_table inner join stage_table
on stage_table.column_key=target_table.column_key;

Strangely the first query gives me a different result then the second query.
What might be the reason and what is best solution in my case.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Deletes no proper counts

Post by nnani » Thu Sep 19, 2013 6:50 pm

Hello ,

I did further investigation and found this
nnani=> select count(a.cntnt_id) from db1.target_table a inner join db2.stage_table b
nnani-> on a.cntnt_id = b.cntnt_id;
count
--------
263602
and then doing this
nnani=> select count(cntnt_id) from db1.target_table
nnani-> where cntnt_id IN
nnani-> (select b.cntnt_id from target_table a inner join db2.stage_table b
nnani(> on a.cntnt_id = b.cntnt_id);
count
-------
89815
(1 row)
How come are these counts different
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Deletes no proper counts

Post by id10t » Thu Sep 19, 2013 7:02 pm

Hi!

So you used in EXISTS or IN?

IN: returns true if a specified value matches any value in a subquery or a list.
Exists: returns true if a subquery contains any rows.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Deletes no proper counts

Post by JimKnicely » Fri Sep 20, 2013 6:28 am

Hi,

This is just a guess, but I wonder if you have duplicates in one or both of the tables?

Code: Select all

dbadmin=> select * from t1;
 c1 
----
  1
  2
  2
(3 rows)

dbadmin=> select * from t2;
 c1 
----
  1
  2
  3
(3 rows)

dbadmin=> select count(t1.c1) from t1 join t2 on t2.c1 = t1.c1;
 count 
-------
     3
(1 row)

dbadmin=> select count(t1.c1) from t1 where exists (select null from t2 where t2.c1 = t1.c1);
 count 
-------
     3
(1 row)

dbadmin=> select count(t2.c1) from t2 join t1 on t1.c1 = t2.c1;
 count 
-------
     3
(1 row)

dbadmin=> select count(t2.c1) from t2 where exists (select null from t1 where t1.c1 = t2.c1);
 count 
-------
     2
(1 row)
Notice how the last query changes based on where the duplicates are...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Deletes no proper counts

Post by id10t » Fri Sep 20, 2013 10:28 am

HI!


I dont see any problem.

Code: Select all

daniel=> select * from rj;
 id 
----
  1
  1
  2
(3 rows)

Code: Select all

daniel=> select * from lj;
 id 
----
  1
  2
  3
(3 rows)

Code: Select all

daniel=> select count(lj.id) from lj where exists (select null from rj where rj.id = lj.id);
 count 
-------
     2
(1 row)

Code: Select all

daniel=> select count(rj.id) from rj where exists (select null from lj where lj.id = rj.id);
 count 
-------
     3
(1 row)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Deletes no proper counts

Post by JimKnicely » Fri Sep 20, 2013 10:32 am

It's not a problem and the query results are correct. It just might be confusing if you don't know you have duplicate values :)

But back to nnani's original question, I think his logic for the delete statement is sound.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Deletes no proper counts

Post by id10t » Fri Sep 20, 2013 10:42 am

Hi!

@jim
When I was replaying I didn't saw your post (that why I "cloned" your post)

Post Reply

Return to “Vertica Database Development”