How to insert a column values from one table to another without having common columns

Tags:
Oracle
Hi i have a created a table with one additional column from another table.

create table myemp as 
select EMP_ID, FIRST_NAME, LAST_NAME,cast(null as number(10)) as "CAST_ID", EMAIL ,PHONE_NUMBER, HIRE_DATE, JOB_ID ,SALARY ,COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID ,C_ID, GENDER, AGE  from My_employees;

all the columns have values except "cast_id" column because this mentioned as null while creating the "myemp" table.

Now I need to insert data in Cast_id column in "myemp" table from another table "cast".

The "cast" table has only one column "cast_id".
There is no common column for these two tables "myemp" and "cast" to insert data.

Insert into myemp (cast_id) select cast_id from cast;

When i was trying to insert those values new rows are created for this values.
but i want to insert those values in the existing rows.

Please guide me to do this..
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    "When i was trying to insert those values new rows are created for this values."

    That means that your table doesn't have a primary key, and all of the columns in the table are nullable.  You should review your design, to make sure you have a reason to have it as it currently is.

    As for your question, you have to have a way to relate mytable with cast, otherwise, how are you going to decide to which mytable's record is assigned each record from cast?

    There must be some criteria.
    84,580 pointsBadges:
    report
  • TheRealRaven
    It's hard to be sure what you are trying to do. From your description, you shouldn't be "inserting" cast_id at all. That will only add new rows.

    It seems like you want to "update" rows rather than "insert" new ones. But you have a bunch of rows in both tables. You have to say how the rows are supposed to match up.

    If you have 100 rows in your myemp table and 80 rows in your cast table, how should they match up? And even if you have the same number of rows, which cast_id goes into which myemp row?

    If you need help, you have to tell us how to match the two tables to each other. If you can us how the two tables should line up together, we can possibly show you how to tell SQL to do it.
    32,940 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: