Results 1 to 2 of 2

Thread: Update multiple rows with unique keys from another table

  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Update multiple rows with unique keys from another table

    Given the following tables:

    Table_A:
    Table_A_key, Table_B_key
    100, null
    101, null

    Table_B:
    Table_B_key, User_Name, User_Type
    300, 'Fred', 'type1'
    301, 'Ethel' 'type1'
    302, 'Brender', 'type2'
    303, 'Eddie', 'type2'

    I want to add Table_B's keys for 'type2' to Table_A's column for Table_B_key. I'd like to use one UPDATE statement or some iteration command to say for all User_Type='type2' UPDATE Table_A's Table_B_key column with Table_B's key. The resulting Table_A should look like:

    Table_A:
    Table_A_key, Table_B_key
    100, 302
    101, 303

    I need to select keys from Table_B based on User_Type='type2' and update the Table_B_key column with the first Table_B key that is in the result list from the select that has not already been added to the Table_B_key column..in Table_A. The problem is getting the Update to run until all rows in Table_A have a unique value from Table_B. Any ideas?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Posts
    1
    I don't think this is possible. You have no way of relating one table to the other. You have defined a foreign key into table 2 from table 1 but it doesn't have any values (yes I realise yo are trying to put some there). Don't forget that a database does not specify what order the tuples(rows) are stored in so the intuitive idea of top to bottom will not work.
    I hope this offers some enlightenment.

Posting Permissions

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