• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Copying data from 1 column of a table to another

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 867
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic