Home » SQL & PL/SQL » SQL & PL/SQL » Inserts
Inserts [message #185622] Wed, 02 August 2006 14:01 Go to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Hi!
I would be very grateful for your help. I need to import data to 3 different tables, which are related by PK & FK. How do I make an insert to multiple columns and eliminatet dubble PK? so that they are still related to the correct FK-row??

I made a try, to the 3 tables...
Author Type Name
4 Alev Lytle 4 History 4 The life of Pharaoes
5 Goran Grow 5 Physics 5 Research of...
6 Goran Grow WRONG! 6 Physics 6 Beginners physics

could this be a solution???
INSERT INTO AUTHOR(ID, NAME)
values (authors_pk_seq.nextval, writer)
SELECT WRITER FROM data@database.com
WHERE ID NOT IN
(SELECT MIN(ID)
FROM author
GROUP BY Name);
Re: Inserts [message #185688 is a reply to message #185622] Thu, 03 August 2006 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could you provide a little more detail.
You talk about 3 tables, but only seem to show one, and the SQL you post is meaningless.

Assuming you are trying to get a distinct set of Authors from your remote database, you could use something like:

(untested)
INSERT INTO AUTHOR(ID, NAME)
SELECT authors_pk_seq.nextval, writer
FROM  (SELECT writer 
       FROM   data@database.com
      MINUS
       SELECT writer
       FROM   author); 
Re: Inserts [message #185700 is a reply to message #185622] Thu, 03 August 2006 03:09 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
ok... So This is how the 3 tables look today(36rows), with some double values for PK. These are related (Table Name_of_book have a FK_ID_TYPE and FK_ID_AUTHOR columns), so if I delete row 6 in Author... what will happen to the FK's ??

Author
4 Alev Lytle
5 Goran Grow
6 Goran Grow ...Delete???


Type
4 History
5 History ...Delete???
6 Physics

Name_of_Book + FK_author + FK_TYPE
4 The life of Pharaoes
5 Research of Pyramids
6 Beginners physics

How would I make an multiple insert into 3 tables(35 rows), delete the double rows (in table Author and Type) at insert... and still not mix up the FK's??
Re: Inserts [message #185707 is a reply to message #185700] Thu, 03 August 2006 03:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
(Hint: It would have been really useful if you could have provided table creation scripts and inserts for this one - then I'd have been able to give you a fully tested reply.)

If your constraints are enabled, you'll find that you can't delete row 6 from Author.

I would do this as a 2 stage operation. Do the inserts and then clean the data.
You'll need to work from the bottom up, setting all the FK constraints to use one PK value, and then deleting unused PKs

This should get all your name_of_book records using the same author if there are duplicates.
(untested)
UPDATE name_of_book nb
SET fk_id_author = (SELECT min(a1.id)
                    FROM   author a1, author a2
                    WHERE  a1.name = a2.name
                    AND    a2.id = nb.fk_id_author)


Then you just need to do a simple duplicate deletion on Author, and I'll let you work that one out for yourself.
Re: Inserts [message #185816 is a reply to message #185707] Thu, 03 August 2006 14:05 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member

Thank you so much!!
I inserted the rows into name_of_BOOK.author_ID from author.ID
Firstly something strange occured.... the rows are placed in the right column but underneath the other column rows.. why?
It doesnt use the first rows...

Then... I got the following result in b.author_id; 1,2,3,4,5,6,7,7,9... using

UPDATE name_of_book b
SET author_id = (SELECT min(a1.id)
FROM author a1, author a2
WHERE a1.name = a2.name
AND a2.id = b.author_id);



AUTHOR
ID NAME
---------- ----------------------
1 Lars Westman
2 Janne Carlsson
3 Ove Torgny
4 Billy Ehn
5 Ulf Bagge
6 Kerstin Skjöld
7 Göran Burenhult
8 Göran Burenhult

9 Lars Hildingson


Would it be possible to delete on of the double rows 7,8 in table Author.ID and Author.name, as wll as the foreign key in name_of_book.author_id ???
Re: Inserts [message #185892 is a reply to message #185816] Fri, 04 August 2006 01:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Thank you so much!!
I inserted the rows into name_of_BOOK.author_ID from author.ID
Firstly something strange occured.... the rows are placed in the right column but underneath the other column rows.. why?
It doesnt use the first rows...

Ummm.... I have no idea what you're trying to say here.
Assume that my psychic powers are in the shop for a service, and that the only things I know about your problem are the words you've written. I don't know what software you're running, I don't know how you're looking at the data, I (still) don't know your table structures.

Quote:

Then... I got the following result in b.author_id; 1,2,3,4,5,6,7,7,9... using

UPDATE name_of_book b
SET author_id = (SELECT min(a1.id)
FROM author a1, author a2
WHERE a1.name = a2.name
AND a2.id = b.author_id);

Assuming that you had had one row for author 7 and one row for author 8 in Name_Of_Book, and that authors 7 and 8 had the same name in the AUTHOR table, then this is the correct result.
All of the FKs now point to author 7, and you can delete author 8 with a standard duplicate deletion process.
Re: Inserts [message #186013 is a reply to message #185892] Fri, 04 August 2006 14:13 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you...
I have one more problem though...
when I insert data into FK_columns in the Name_of_BOOK.. values of different columns are placed below each other... This due to sequence or PK??? Below is the last values of column authorID at right(52, 53) and the next column type_ID(at left) start by 10, 20, 30 ...BUT BELOW ??

53


54


10


20


30
Re: Inserts [message #186085 is a reply to message #185622] Sat, 05 August 2006 11:01 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
please please... enclosed a file "book_strange.txt". I just dont understand why the values of FK_tables TYPE_ID SUPPLIER_ID AUTHOR_ID are placed far below the rest of teh other table values.

Re: Inserts [message #186086 is a reply to message #186085] Sat, 05 August 2006 12:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Quote:

Below is the last values of column authorID at right(52, 53) and the next column type_ID(at left) start by 10, 20, 30 ...BUT BELOW ??

You can format code and results with [code] tags, which you can do using the handy "{...}" button in the "formatting tools" button bar when posting.

I've looked at the text file you provided but I'm sorry, I still have no idea what you're talking about. Some sort of formatting issue in SQL*Plus maybe? Are you using SQL*Plus?

[Updated on: Sat, 05 August 2006 12:06]

Report message to a moderator

Re: Inserts [message #186097 is a reply to message #186085] Sat, 05 August 2006 16:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
We cannot understand what you are asking. Please try to phrase your problem more clearly. Pleaase read the sticky at the top of the forum for what we expect, then post create table and insert statements for sample data, then an example of what results you want. You have mentioned importing and inserting. Are you trying to import or insert? Where are you getting the data from, a text file or multiple text files or a table or 3 tables? Are you trying to insert from 1 table into 3 tables using a multi_table insert or are you trying to insert from 3 tables into 1 table using join conditions? Have you already done the insert, but it did not come out the way you wanted it and you are trying to fix it using updates? If so, it may be better to redo the insert correctly, than try to fix what you have. Do you need the data in a table or just displayed in a report? Please clarify.
Re: Inserts [message #186114 is a reply to message #186097] Sun, 06 August 2006 02:01 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Sorry for being so unclear... Embarassed ...last try.
Yes I use sql*Plus. I have inserted values into table TYPE_OF BOOK from a DB, using (SEQUENCE type_of_book_pk_seq
INCREMENT BY 10 and TRIGGER type_of_book_trg) creating new ID values. I have tried to insert these ID values as foreign key into (TYPE_ID) to table BOOK, and they are placed below.(check lowest down in the enclosed file)

INSERT INTO BOOK(TYPE_ID)
SELECT ID FROM TYPE_OF_BOOK;

I think its due to the SEQUENCE... some format problem
  • Attachment: hope.txt
    (Size: 11.49KB, Downloaded 163 times)
Re: Inserts [message #186127 is a reply to message #186114] Sun, 06 August 2006 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"they are placed below" -> where would you want to have them? Above? "SELECT * FROM book ORDER BY type_id" will bring them up, but I guess that's not the point here - I believe that you, actually, wanted to UPDATE the table, not INSERT records into it (which you, actually, did).

Therefore, perhaps you should try with something like
UPDATE book b SET
b.type_id = (SELECT t.id FROM type_of_book t
             WHERE t.some_column = b.some_column
            )

and, optionally, 

WHERE EXISTS (...)
Re: Inserts [message #186128 is a reply to message #186127] Sun, 06 August 2006 05:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Nice one Littlefoot, that could actually be it Wink
Re: Inserts [message #186226 is a reply to message #186085] Mon, 07 August 2006 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How did you generate this .txt file? It doesn't look like a straight select (or if it is, it looks like something has dome some manipulation on it before you posted it.

If you used sql*Plus, then try again, only this time, issue the command 'SET LINESIZE 1000' before hand. This should get rid of the problem of fields wrapping round onto a second line.
Re: Inserts [message #186229 is a reply to message #186114] Mon, 07 August 2006 02:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think littlefoot is right - it looks like you have inserted a lot of rows with only one value in them.
To check if this is the case, could you run the select from SQL*Plus again, only this time issue the following commands first:
set colsep |
set null null

These will make the start and end of columns more visible, and show you where you've got null values.
Re: Inserts [message #186354 is a reply to message #186229] Mon, 07 August 2006 11:37 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Hi!
Thank you very much for your time and effort.
Actually, I got further instructions today...
I will re-insert the values from the DB(synonym Grossisten)firstly to the TYPE_ID SUPPLIER_ID AUTHOR_ID, so that I create the PK_seq_ID_values for the table BOOK.

Then I will re-insert into table BOOK from DB+ and the PK_ID_values into the foreign key columns of BOOK. But now I am confused because I need to define/create a sentence that picks the right row in Grossisten which corresponds to same row of PK_ID_columns in the TYPE_ID, SUPPLIER_ID, AUTHOR_ID.

INSERT INTO Book
SELECT (isbn from Grossisten),
title from Grossisten),
in_store from Grossisten),
price from Grossisten),
id from author where "row of Grossisten which corresponds to same row in AUTHOR" Embarassed How do I define a row??
id from supplier
id from type_of_book;


Re: Inserts [message #186374 is a reply to message #186354] Mon, 07 August 2006 13:59 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to join the tables on some common column. Please see the partial example below.

-- data before insert into book:
SCOTT@10gXE> SELECT * FROM Grossisten
  2  /

ISBN   AUTHOR   SUPPLIER
------ -------- ----------
isbn_a author_d supplier_f
isbn_b author_d supplier_g
isbn_c author_e supplier_g

SCOTT@10gXE> SELECT * FROM author
  2  /

 AUTHOR_ID AUTHOR
---------- --------
         1 author_d
         2 author_e

SCOTT@10gXE> SELECT * FROM supplier
  2  /

SUPPLIER_ID SUPPLIER
----------- ----------
          1 supplier_f
          2 supplier_g

SCOTT@10gXE> SELECT * FROM book
  2  /

no rows selected


-- insert into book:
SCOTT@10gXE> INSERT INTO book (isbn, author_id, supplier_id)
  2  SELECT -- columns to insert, prefaced with the tables they come from:
  3  	    Grossisten.isbn,
  4  	    author.author_id,
  5  	    supplier.supplier_id
  6  FROM   -- tables to select from:
  7  	    Grossisten,
  8  	    author,
  9  	    supplier
 10  WHERE  Grossisten.author = author.author	    -- this is a join condition
 11  AND    Grossisten.supplier = supplier.supplier -- this is a join condition
 12  /

3 rows created.


-- data after insert into book:
SCOTT@10gXE> SELECT * FROM book
  2  /

ISBN    AUTHOR_ID SUPPLIER_ID
------ ---------- -----------
isbn_a          1           1
isbn_b          1           2
isbn_c          2           2

SCOTT@10gXE> 



Previous Topic: Mutating trigge Issue...
Next Topic: Pass Append 'pipes' || as part of a string
Goto Forum:
  


Current Time: Fri Dec 09 00:02:34 CST 2016

Total time taken to generate the page: 0.09016 seconds