Comparing two tables

Cketha · New User Joined: 19 May 2007 Posts: 22 Location: mumbai

Hi,

We have to compare two tables F1,F2 on pin_cd,pin_nm key.If a particuar record is present in the table F1 and not present in the F2 that particular record have to be deleted from the F2.Please refer to the below example.

F1
---
Pin_cd Pin_nm Pin_Desc
------- ------- -----------
01 acd past
02 mmm future
03 kcd present

F2
--
Pin_cd Pin_nm Pin_Desc
------- ------- -----------
01 acd past
02 mmm future
05 aaa present

In the above example 05 record has to be deleted from F2.
WE SHOLUD NOT USE ANY SUB QUERIES FOR COMPARING

Thanks

enrico-sorichetti · Posted: Fri Feb 08, 2008 7:35 pm

Cketha · New User Joined: 19 May 2007 Posts: 22 Location: mumbai

Hi,

We have done it already thru the sub queries ,but we were given a task to do it thru the join without using sub queries ,inorder to know which process is efficient interms of cost.

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

If you have used a correlated subquery.. somthing like
Delete from F2 T1
where not exists ( select 1 from f1 T2
T2.Pin_cd = T1.Pin_cd
T2.pin_nm = T1.pin_nm ) ;.. Then I feel it is good a solution. Also you cannot use join directly on a delete query as the delete operation applies only one table.

Hope this helps.

vsgaikwad · New User Joined: 20 Nov 2007 Posts: 6 Location: Pune

Hi Prajesh,

I tried your code but it's not working.

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "T1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: UNION
SQLSTATE = 42601 SQLSTATE RETURN CODE

Can u clarify what is missing here.

vsgaikwad · New User Joined: 20 Nov 2007 Posts: 6 Location: Pune

Hi,
I tried following & now it's working fine.

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

"We have to compare two tables F1,F2 on pin_cd,pin_nm key"..

It seems like u are comparing only emp no?. Good enough if thats ur requirement..

I wud suggest the sub query to change to below as u need only an existence check..
(
SELECT 1 FROM B
WHERE B.EMP_NO = A.EMP_NO);

Thanks,
Prajesh

vsgaikwad · New User Joined: 20 Nov 2007 Posts: 6 Location: Pune

Hi Prajesh,

I tried above code mine & also given by you.
My query ran without error even your query also, but I seen that records are not deleted from table B.

Even I tried

SELECT * FROM A
WHERE NOT EXISTS (
SELECT * FROM B
WHERE B.EMP_NO = A.EMP_NO);

It's giving SQLCODE 100.

But when I tried

SELECT * FROM A INNER JOIN B ON
A.AC_NO != B.AC_NO;

It's giving many records. It's meaning is that records from A not get deleted. But I don't understand why it is not get deleted.

Thanks,
Vikas G.

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

Its getting deleted.. But ur validation query is wrong..

SELECT * FROM A INNER JOIN B ON
A.AC_NO != B.AC_NO;

It would give many rows as each account number record can find at least 1 non matching record for account number. Hope u r getting this..

vsgaikwad · New User Joined: 20 Nov 2007 Posts: 6 Location: Pune

Could u please explain little bit more, how this Query is invalid & what I am missing.

Vikas

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

Table A Table B
Act1 Act1
Act2 Act2

When the condition check for row1 comes,record from table A ie Act1, it can find at least one non matching record in table B ie Act2. hence it retrievs the row1..so on for row2. If you are still in confusion, you might have to go back to the manuals for join condition...