• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
  • Paul Clapham
Sheriffs:
  • paul wheaton
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Piet Souris
Bartenders:
  • Mike London

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: 17347
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
-----Copy only multiple columns from one table into another table with where condition: This has been tested in Oracle DB
Note: the brackets are very important in clause like  SET(column1, column2, ....)   other wise we will get an error

UPDATE table1 t1
   SET (t1.city, t1.state) =
(SELECT t2.city, t2.state
  FROM table2 t2
 WHERE t1.pk_column = t2.pk_column);
 
reply
    Bookmark Topic Watch Topic
  • New Topic