-
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!
-
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
-
Forum Rules
|
|