Copying data from 1 column of a table to another

[Rebecca Abraham]

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

[JulianInactive KennedyInactive]

Hi Rebecca,

Your code needs to look something like this:

INSERT
INTO   info
( 
       city
      ,state
)
SELECT city
      ,state
FROM   profile
WHERE  profile.pk_column = info.fk_column

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]

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"):

 
INSERT INTO bonuses
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25 * salary;
 

For futher details, you can click here to read.

Hope this help

[Mark Spritzler]

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.

 UPDATE info
    SET city, 
        state =
(SELECT city,
        state
   FROM profile
  WHERE profile.pk_column = info.fk_column)

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.

 UPDATE info
    SET city =
(SELECT city,
   FROM profile
  WHERE profile.pk_column = info.fk_column)

 UPDATE info
    SET state =
(SELECT state
   FROM profile
  WHERE profile.pk_column = info.fk_column)

Mark

[JulianInactive KennedyInactive]

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:

UPDATE info
SET    city  = (SELECT city
                FROM   profile
                WHERE  profile.pk_column = info.fk_column)
      ,state = (SELECT state
                FROM   profile
                WHERE  profile.pk_column = info.fk_column)

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

[Aravind Apagonde]

-----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);