Home » SQL & PL/SQL » SQL & PL/SQL » How to combine 2 fields from 1 table and insert it into another table?
How to combine 2 fields from 1 table and insert it into another table? [message #363008] Thu, 04 December 2008 15:23 Go to next message
tayam
Messages: 2
Registered: December 2008
Junior Member
BOOKS Table
No            Name             Stock             Author
11           Oracle             Y              Adam Smith
12           Java               N              David Lee
13            C++               Y              Nancy White
			
AUTHOR Table
ID             F_Name              L_Name              Books
1               Joe                 Black             Finance
2               Bill                Chester           Accounting
3               Mike                James             History

There are two tables, BOOKS and AUTHOR.

Now,

We need to populate BOOKS table using AUTHOR table.

First, the code should pull all the values from ‘Books’ field in AUTHOR table and put them under ‘Name’ field in BOOKS table.

Next...

The code should combine ‘F_Name’ and ‘L_Name’ fields in AUTHOR table and put it under ‘Author’ field in BOOKS table.

The rows under ‘No’ field in BOOKS table should also increase accordingly.


After the code runs successfully, the BOOKS table should look something like this….
No             Name                         Author
11             Oracle                      Adam Smith
12             Java                        David Lee
13              C++                        Nancy White
14             Finance                     Joe Black
15             Accounting                  Bill Chester
15             History                     Mike James

This is what I did... but it's not working!!!

Create or replace sequence Novel_Seq

Create or replace procedure Update_Novel as

   cursor c1 is select (F_Name||' '||L_Name) as Writer from AUTHORS where Books is            not NULL;

   v1 c1%rowtype;

Begin

   open c1;

   loop
      fetch c1 into v1;
      exit when c1%notfound;

         insert into BOOKS (No, Name, Author) 
             values (Novel_Seq.nextval, v1.Books, v1.Writer);

   end loop;

   commit;
 
   dbms_output.put_line ('Table has been updated!!!');

   close c1;

End;
/


[MERGED by LF; applied [code] and [pre] tags]

[Updated on: Thu, 04 December 2008 16:28] by Moderator

Report message to a moderator

Re: How to combine 2 fields from 1 table and insert it to another table? [message #363018 is a reply to message #363008] Thu, 04 December 2008 15:50 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Hi,

The cursor should be defined like this perhaps:

cursor c1 is select Books, (F_Name||' '||L_Name) as Writer from AUTHOR where Books is not NULL;


This could also all be done in a single INSERT...

insert into books
select Novel_Seq.nextval,
Books,
'Y',
F_Name || ' ' || L_Name
from author;


I did that INSERT in 11g.

Note, I added a 'Y' to indicate that the book is in stock...

Thanks,
Jim
Re: How to combine 2 fields from 1 table and insert it to another table? [message #363022 is a reply to message #363008] Thu, 04 December 2008 16:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why PL/SQL? This can be done using pure SQL.

Why using a SEQUENCE (as a database object)? As your example says, new records' books.no column value is "maximum current BOOKS table's NO column value + AUTHOR's ID column value". If you still want to use a sequence, it seems that starting from 1 is not a good idea. Also, if you chose to share your code, why wouldn't you use exact code? This wouldn't even run! (at least, not up to Oracle 10g):
SQL> create or replace sequence novel_seq;
create or replace sequence novel_seq
                  *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> create sequence novel_seq;

Sequence created.

SQL>
Furthermore, how can you reference "v1.BOOKS" when BOOKS is not selected in cursor's SELECT statement?

Saying that your code "is not working" is quite meaningless; what does it, actually, mean? What happens? Do you expect us to guess?

Moreover, why didn't you provide test case (CREATE TABLE as well as INSERT INTO sample data statements), and - obligatory - code which compiles successfully; yours, as far as I can tell, doesn't. Information you provided is not properly formatted; even if it was, you didn't use [code] tags to preserve formatting and make reading easier.

A conclusion: you failed to do the job because you did it wrong. What was wrong? Everything! It is useless to comment a non-compiling code. At least, why wouldn't you consult documentation, start over and - once you produce code which can be reproduced - return back here and I'm sure help will be provided. Quid pro quo.
Re: How to combine 2 fields from 1 table and insert it to another table? [message #363024 is a reply to message #363022] Thu, 04 December 2008 16:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; too much complaining for nothing. That's some good tactics! Multiply your questions and someone will do the job for you! ./fa/3943/0/ Yummy!
Re: How to combine 2 fields from 1 table and insert it into another table? [message #363276 is a reply to message #363008] Fri, 05 December 2008 11:53 Go to previous messageGo to next message
tayam
Messages: 2
Registered: December 2008
Junior Member
Thanks, knicely87!!!
1800-YOU-ROCK

This is what I did.... I worked perfectly... Thanks again!!!


Create or replace sequence Update_Seq;

Create or replace procedure Update_Novel as

cursor c1 is select * from AUTHORS where Books is not NULL;

v1 c1%rowtype;

Begin

open c1;

loop
fetch c1 into v1;
exit when c1%notfound;

insert into NOVEL (No, Name, Author)
Values (Update_seq.nextval, v1.Books, v1.FIRST_Name||' '||v1.LAST_Name);

end loop;

commit;

dbms_output.put_line ('Table has been updated!!!');

close c1;

End;
/

Re: How to combine 2 fields from 1 table and insert it into another table? [message #363283 is a reply to message #363276] Fri, 05 December 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1800-YOU-ROCK

Is this your phone number?

Please read OraFAQ Forum Guide:
- Don't use IM speak
- but format your post.

Regards
Michel
Re: How to combine 2 fields from 1 table and insert it into another table? [message #363291 is a reply to message #363008] Fri, 05 December 2008 12:50 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Great. Now everyone knows my cell number. Embarassed
Previous Topic: user paramter @
Next Topic: Problem querying node values from a XMLType table defined from a schema.
Goto Forum:
  


Current Time: Thu Dec 08 20:05:56 CST 2016

Total time taken to generate the page: 0.13594 seconds