Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Question Unanswered: Update one table index with the column from another table

    Hello,

    I have a table with SSN and AlternateId columns, this table is loaded from a text file and we must use this table (1st table) to update another large table (2nd table) that includes the SSN and AlternateId columns as indexes.

    I need to have the 1st table drive the process of updating the 2nd table AlternateId index by reading the 1st table and updating the 2nd table for the same SSN (1st.ssn = 2nd.ssn) with AlternateId from the 1st table.

    I am trying to use a straight SQL statement that can do this, your help with suggestions/solutions would greatly appreciated!

    Thanks
    Rnamro

  2. #2
    Join Date
    Nov 2005
    Posts
    17
    An example of table structure, data, and desired results would be helpful. Are you inserting new rows into table2 or updating existing data?

  3. #3
    Join Date
    Nov 2005
    Posts
    4
    sorry here are some details:

    Table1
    SSN ---- key column
    AlternateID

    Table2:
    col1
    col2
    col3
    SSN ---- column is a non unique index (currently populated)
    AlternateID ---- column is a non unique index (currently empty/null)
    col6
    etc ...

    Table1 is loaded from a file.
    using the rows in table one,
    update the AlternatId column in table2
    (table2.AlternateId = table1.AlternateId)
    where table1.SSN = tabl2.SSN

    hope this helps clarify things.
    Thanks for the help
    Rnamro

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    update table2 A
    set A.AlternateId = (SELECT B.AlternateId
    FROM TABLE1 B
    WHERE A.SSN = B.SSN)
    WHERE EXISTS
    (SELECT NULL
    FROM TABLE1 C
    WHERE A.SSN = C.SSN);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2005
    Posts
    4
    Thanks for the help, but ...
    the result from the subquery returns more than one record.
    that's the error i get when i run it...

    help would still be appreciated.

    Thanks
    Riad

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Sounds like Table1.SSN may not be unique.

    To check, try
    Code:
    select ssn
    from Table1
    group by ssn
    having count(*) > 1
    -cf

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Sounds like Table1.SSN may not be unique.
    OP stated in original/first post "SSN ---- column is a non unique index "
    So I suspect you are both correct.
    What has been learned so far?
    SSN ---- column is a non unique index
    Lather, Rinse, Repeat.
    Lather, Rinse, Repeat.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    ssn is described non-unique index in table 2, but is only defined as a "key" in table 1, which is why I inferred that it was unique. At least that how I read it.

    -cf

  9. #9
    Join Date
    Nov 2005
    Posts
    17
    If you can guarantee a 1:1 relationship with ssn and alternateid in table1 to table2 you could use something like this. If you cannot guarantee this, you could use something similar with a MERGE statement.


    Code:
    DROP TABLE x CASCADE CONSTRAINT PURGE;
    CREATE TABLE x
     (ssn         VARCHAR2(11)
     ,alternateId NUMBER);
    
    DROP TABLE y CASCADE CONSTRAINT PURGE;
    CREATE TABLE y
     (ssn         VARCHAR2(11)
     ,alternateId NUMBER);
    
    INSERT INTO x VALUES ('111-22-3333', 10);
    INSERT INTO x VALUES ('111-22-3333', 20);
    INSERT INTO x VALUES ('111-22-3333', 30);
    INSERT INTO x VALUES ('999-88-7777', 40);
    INSERT INTO x VALUES ('999-88-7777', 50);
    INSERT INTO x VALUES ('999-88-7777', 60);
    
    
    INSERT INTO y VALUES ('111-22-3333', NULL);
    INSERT INTO y VALUES ('111-22-3333', NULL);
    INSERT INTO y VALUES ('111-22-3333', NULL);
    INSERT INTO y VALUES ('999-88-7777', NULL);
    INSERT INTO y VALUES ('999-88-7777', NULL);
    INSERT INTO y VALUES ('999-88-7777', NULL);
    
    COMMIT;
    
    SQL> SELECT * FROM x;
    
    SSN         ALTERNATEID
    ----------- -----------
    111-22-3333          10
    111-22-3333          20
    111-22-3333          30
    999-88-7777          40
    999-88-7777          50
    999-88-7777          60
    
    6 rows selected.
    
    SQL> SELECT * FROM y;
    
    SSN         ALTERNATEID
    ----------- -----------
    111-22-3333
    111-22-3333
    111-22-3333
    999-88-7777
    999-88-7777
    999-88-7777
    
    6 rows selected.
    
    SQL> UPDATE y
      2  SET alternateId = (SELECT a.alternateId
      3                     FROM (SELECT ROW_NUMBER() OVER(ORDER BY ssn) rnum
      4                                 ,ssn
      5                                 ,alternateId
      6                           FROM x) a
      7                         ,(SELECT ROW_NUMBER() OVER(ORDER BY ssn) rnum
      8                                 ,ssn
      9                                 ,alternateId
     10                                 ,ROWID r_id
     11                           FROM y) b
     12                      WHERE a.rnum = b.rnum
     13                        AND a.ssn = b.ssn
     14                        AND y.ROWID = b.r_id);
    
    6 rows updated.
    
    SQL> SELECT * FROM y;
    
    SSN         ALTERNATEID
    ----------- -----------
    111-22-3333          10
    111-22-3333          20
    111-22-3333          30
    999-88-7777          40
    999-88-7777          50
    999-88-7777          60
    
    6 rows selected.

  10. #10
    Join Date
    Nov 2005
    Posts
    4
    something else to add...
    table1 is droped and repopulated periodically with records that exist in table2,
    the row number in table1 does not correspond to the row number in table 2, further, the records in table1 are not in the same order or sequence of rows in table2.

    Thanks

  11. #11
    Join Date
    Nov 2005
    Posts
    17
    The row order is irrelevent. The use of ROW_NUMBER was simply a means of mapping a row in table1 to a row in table2 with the same SSN. If there must be a specific row in table1 to table2 by a condition other than SSN you should include that information; otherwise, there is no way to join the rows that I am aware of. Something that may help is provide sample data from table1 and table2, along with your desired output.

Posting Permissions

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