Hello, I'm stucked with trying to identify records that are already present
in a table when queried from another table...
For example, if I have 2 tables (Table A and Table B), Table B has 3 fields
(a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
into Table B from Table A if they are not already present... here's my sql
statement
insert into TableB
(a,b,c)
select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)
its returning me no records as inserted because
[select a,b,c from TableA
where
TableA.a not in (select a from TableB)
and TableA.b not in (select b from TableB)]
is not identifying the records that are not in. If I have only 1 primary key
then there's no problem.
Can anyone point out what's wrong with this statement? Thanks a lot in
advance.I think this will work:
select * from TableA
where not exists(select * from TableB
where TableB.a = TableA.a
and
TableB.b = TableA.b)
Bryce|||try
insert into TableB
(a,b,c)
select a,b,c from TableA
where
not exists (select 1 from TableB where TableB.a = TableA.a and TableB.b and
TableA.b)
"Nestor" wrote:
> Hello, I'm stucked with trying to identify records that are already presen
t
> in a table when queried from another table...
> For example, if I have 2 tables (Table A and Table B), Table B has 3 field
s
> (a,b,c) out of which (a,b) are primary keys. I'm trying to insert records
> into Table B from Table A if they are not already present... here's my sql
> statement
> insert into TableB
> (a,b,c)
> select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)
> its returning me no records as inserted because
> [select a,b,c from TableA
> where
> TableA.a not in (select a from TableB)
> and TableA.b not in (select b from TableB)]
> is not identifying the records that are not in. If I have only 1 primary k
ey
> then there's no problem.
> Can anyone point out what's wrong with this statement? Thanks a lot in
> advance.
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment