Page 1 of 1
Deletes no proper counts
Posted: Thu Sep 19, 2013 6:19 pm
by 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.
Re: Deletes no proper counts
Posted: Thu Sep 19, 2013 6:50 pm
by 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
Re: Deletes no proper counts
Posted: Thu Sep 19, 2013 7:02 pm
by 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.
Re: Deletes no proper counts
Posted: Fri Sep 20, 2013 6:28 am
by 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...
Re: Deletes no proper counts
Posted: Fri Sep 20, 2013 10:28 am
by 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)
Re: Deletes no proper counts
Posted: Fri Sep 20, 2013 10:32 am
by 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.
Re: Deletes no proper counts
Posted: Fri Sep 20, 2013 10:42 am
by id10t
Hi!
@jim
When I was replaying I didn't saw your post (that why I "cloned" your post)