Home » SQL & PL/SQL » SQL & PL/SQL » populate table
populate table [message #194495] Fri, 22 September 2006 06:58 Go to next message
gtriant
Messages: 41
Registered: September 2006
Member
i am trying to populate a table (table_1) which has two columns (column_a, column_b).
The columns are coming from two other tables (table_2, table_3) and i use the following query:


INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
WHERE NOT EXISTS
(SELECT * FROM table_1)

the result from this is "0 rows inserted", although there are plenty of entries to be entered.

what's wrong?
Re: populate table [message #194498 is a reply to message #194495] Fri, 22 September 2006 07:08 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
you have given
INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
[B]WHERE NOT EXISTS[/B](SELECT * FROM table_1)



use this

INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
Re: populate table [message #194499 is a reply to message #194495] Fri, 22 September 2006 07:20 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
There is a unique constraint, that forbids two same entries to be entered and thus with the query you proprsed comes the expected error
(i forgot to mention that, by i thought it was obvious that i want to insert the "remaining" entries avoiding two same entries in the table)
Re: populate table [message #194503 is a reply to message #194499] Fri, 22 September 2006 07:33 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
where column_a.table_2 not in column_a.table_1
and column_b.table_3 not in column_a.table_1
Re: populate table [message #194506 is a reply to message #194503] Fri, 22 September 2006 07:44 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You are giving a query with a cartesian product.
Re: populate table [message #194509 is a reply to message #194495] Fri, 22 September 2006 07:59 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
I am aware that i am giving a query with a cartesian product. This is what intend to do. but i also want to avoid the entries that already exist.

Any ideas???
Re: populate table [message #194511 is a reply to message #194509] Fri, 22 September 2006 08:03 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
thats why we need to use where condition
Re: populate table [message #194513 is a reply to message #194495] Fri, 22 September 2006 08:13 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
BUT I AM USING A WHERE CONDITION Mad
Re: populate table [message #194519 is a reply to message #194495] Fri, 22 September 2006 08:27 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
To clarify things:

I want to populate a table (table_1) that has two columns (column_a,column_b), with the cartesian product of two same columns that exist in two other tables (table_2 with a column named column_a, and table_3 with a column named column_b). But i want to insert only the records that are not already inserted.
I hope this is more clear.
Re: populate table [message #194521 is a reply to message #194495] Fri, 22 September 2006 08:35 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You are but you where condition says to insert rows when no rows exist in table_1

WHERE NOT EXISTS
(SELECT * FROM table_1)


Since table_1 has rows the where condition fails. You probably need something like:

SQL> select * from table_1;

C C
- -
a b
a c
a d
b c
b d

SQL> select * from table_2;

C
-
a

SQL> select * from table_3;

C
-
a
b
c
d




SQL>  select a.column_a, b.column_b
  2   from table_2 a, table_3 b
  3   where not exists (select 1
  4                     from table_1
  5                     where column_a = a.column_a
  6                       and column_b = b.column_b)
  7  /

C C
- -
a a

[Updated on: Fri, 22 September 2006 08:35]

Report message to a moderator

icon5.gif  Re: populate table [message #194623 is a reply to message #194521] Sat, 23 September 2006 01:29 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
does it work now?or some more spoon-feeding?
Re: populate table [message #194903 is a reply to message #194503] Tue, 26 September 2006 02:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
where column_a.table_2 not in column_a.table_1
and column_b.table_3 not in column_a.table_1


What particular database was this intended to work on, because it's certainly not Oracle.
Re: populate table [message #194904 is a reply to message #194521] Tue, 26 September 2006 02:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the amount of data you have is large, you'd probably be better off with:
(untested)
INSERT INTO table_1 (column_1,column_2)
SELECT distinct column_a,column_b 
FROM (SELECT a.column_a, b.column_b
      FROM   table_2 a, table_3 b
     MINUS
      SELECT column_1,column_2
      FROM   table_1)
Re: populate table [message #194921 is a reply to message #194495] Tue, 26 September 2006 03:15 Go to previous message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
YOUR ANSWER:
INSERT INTO table_1
SELECT column_a.table_2, column_b.table_3 FROM table_2, table_3
WHERE NOT EXISTS
(SELECT * FROM table_1)

EXPLAINATION:

THIS NOT EXISTS OPERATOR HAVE TO BE USED WITH CORRELATED SUBQUERY.
THE MAIN QUERY COLUMN MUST BE PASSED IN TO THE SUBQUERY WHEN YOU USE EXISTS (OR) NOT EXISTS OPERATOR.INSTEAD OF USING IN OPERATOR WE WILL BE USING EXISTS FOR PERFORMANCE SAKE.
Previous Topic: Inheritance!!!!!!
Next Topic: Bad optimisation of left outer join
Goto Forum:
  


Current Time: Sat Dec 10 14:36:01 CST 2016

Total time taken to generate the page: 0.21783 seconds