Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011

    Unanswered: SQL Experts need help - oracle SQL

    Table: College - it stores colleges and universities.

    ID : long Primary key : Surrogate primary key
    CODE : Varchar , unique, nullable : The unique code for a college/university
    PAR_CODE : Varchar, nullable : The university code to which this college belongs. null if its university.
    PAR_ID : long foreign key to self referencing ID column, nullable : This is the new column added to the table. It is the ID of the university to which this college belongs. null if its university

    The main purpose is to eventually to drop the PAR_CODE column, but after we populate the PAR_ID column with the corresponding ID value.

    Is there any SQL which can do this ? In short :

    For each row in College Table {
    set thisRow.PAR_ID = (select ID from COLLEGE where CODE = thisRow.PAR_CODE)

    Any help would be appreciated


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    UPDATE college
      SET par_id = (SELECT id FROM college c2 WHERE c2.code = par_code)
    But this will only work, if CODE is unique and thus the sub-select returns exactly one row.
    If this is not the case you will need to decide which ID to use (e.g. min/max/...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts