File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Copying data from 1 column of a table to another Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Copying data from 1 column of a table to another" Watch "Copying data from 1 column of a table to another" New topic
Author

Copying data from 1 column of a table to another

Rebecca Abraham
Ranch Hand

Joined: Feb 20, 2003
Posts: 37
Hi,

In my Oracle database, I have 2 tables info & profile. The profile table has the columns city & state. I want to add the these columns to the info table & also populate these columns in the info table with the same data as in profile table. I have added the columns. But I am not able to copy the data in these columns from the profile table to the info table. Couls someone please help? Its really very urgent.

Thanks.

Rebecca
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Rebecca,

Your code needs to look something like this:


Seem OK?

Just a friendly observation: it's best not to say it's urgent if you want help on here as it tends to make people ignore your post.

Jules
Francis Siu
Ranch Hand

Joined: Jan 04, 2003
Posts: 867
hi Rebecca Abraham
You can take a look with the following example that copy from oracle website

Inserting Values with a Subquery: Example
The following statement copies employees whose commission exceeds 25% of their salary into the bonuses table (which is created in "Merging into a Table: Example"):


For futher details, you can click here to read.

Hope this help


Francis Siu
SCJP, MCDBA
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

Rebecca, is there already data in the info table. If so, what they posted will not work, it will add all new records for those in the profile table.

What you need is there to be a link between the two tables so that you can perform an Update statement. If there is no link, then you won't be able to do a join that will create the data the way you would like it.

Julian is pretty close, but made it an insert statement instead of an update statement.



I haven't written Oracle SQL in about 5 months, so I don't remember if you can set two columns like that or if you need the subquery twice, once for each field.

Here's the other way just in case, but it would take longer.





Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I haven't written Oracle SQL for over 2 years, but that's no excuse for getting this wrong! Thanks Mark for pointing it out and, apologies to Rebecca if that just made your headache worse!

I an attempt to partially redeem myself I'll say that I'm pretty sure you can't use the syntax in the first example but you can combine the other two as follows:

Looks horrible, doesn't it? Unfortunately this is the ANSI standard syntax and the only way that Oracle provides for doing this kind of thing.

Other RDBMS such as Sybase & MS SQL Server add a FROM clause to the UPDATE syntax, which greatly simplifies this kind of statement. MySQL allows you to specifiy more than one table in the UPDATE clause; the first being the one that is updated. Unfortunately the above method is the only portable way of doing it (and even then I'm not sure if it'll work in MySQL...)

Hope that's useful/interesting.

Jules
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Copying data from 1 column of a table to another