Deletes no proper counts

[nnani]

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]

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

[id10t]

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.

[JimKnicely]

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

[id10t]

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)

[JimKnicely]

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.

[id10t]

Hi!

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