How to combine 2 fields from 1 table and insert it into another table? [message #363008] |
Thu, 04 December 2008 15:23  |
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   |
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   |
 |
Littlefoot
Messages: 21823 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 into another table? [message #363276 is a reply to message #363008] |
Fri, 05 December 2008 11:53   |
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;
/
|
|
|
|
|