Home » SQL & PL/SQL » SQL & PL/SQL » SQL to Rebuild the references of a table (Oracle 8i and previous)
SQL to Rebuild the references of a table [message #306565] Fri, 14 March 2008 12:48
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Heh... it's been a while since I posted on this amazing forums.

Anyways, I have a small problem (actually, is just curiosity)... I want to see if it was possible to come with a solution for this situation, with 1 single SQL.

Here is the "problem", I have 2 tables:
Create table Table1 (
 ID_Table1 Number(3)
);

Create table Table2 (
 ID_Table2 Number(5),
 ID_Table1 Number(3),
 Some_Code Varchar2(1)
);

Yeah, Table2 references Table1.



For the data, something like this:
---------------------------------------------------------------------
Table1:

ID_TABLE1
1
2
3
4
5

Insert into table1 values(1);
Insert into table1 values(2);
Insert into table1 values(3);
Insert into table1 values(4);
Insert into table1 values(5);

Assume that ID_TABLE1 is sequential, with no gaps.

---------------------------------------------------------------------
Table2:

ID_Table2	ID_Table1	Some_Code
1		1		A
2		1		A
3		2		B
4		2		C
5		3		C
6		3		A
7		3		B
8		4		A
9		4		C
10		4		B
11		5		B


Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(1, 1, A);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(2, 1, A);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(3, 2, B);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(4, 2, C);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(5, 3, C);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(6, 3, A);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(7, 3, B);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(8, 4, A);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(9, 4, C);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(10, 4, B);
Insert into table2 (ID_Table2, ID_Table1, Some_Code) values(11, 5, B);


-----------------------------------------------------------------------



Now, I'm looking for a "reconstruction" of Table2. The idea is to reassign ALL its ID_Table1 codes, following the next rules:
- The new ID_Table1 must be random. In other words, I don't mind what ID_table1 I assign to any ID_Table2... as long as, it exists on TABLE1.
- All "Some_Code" share the same value for the ID_Table1 code designated.

There is no problem if I have to reinsert all Table2 records. So, the solution could be a simple UPDATE, or a Backup -> Delete -> Insert Select. (Naturally, the UPDATE option should be more interesting)

For example:

From this:
ID_T2 ID_T1 Some_Code
1     1     B     
2     1     B
3     2     B 
4     2     A

To this:
ID_T2 ID_T1 Some_Code
1     1     B     
2     1     B
3     1     B 
4     2     A

or this:
ID_T2 ID_T1 Some_Code
1     2     B     
2     2     B
3     2     B 
4     1     A

(Remember it is random)

Well, that's all. Thanks for your time.
Important note: I wasn't able to test the posted scripts (No Oracle Around)

Edit: Wow, re-reading this post I noticed I posted this on an extremely wrong "tone", fixed and sorry about that.

Anyway, this was part of a past work where we had to prepare some data for some nasty tests. Sadly the only solution we had at the time was extremely poor... naturally, we ended questioning ourselves how to do it right.

[Updated on: Fri, 14 March 2008 15:14]

Report message to a moderator

Previous Topic: Parse string from CSV using Utl_file read
Next Topic: ORA-00936: Missing Expression
Goto Forum:
  


Current Time: Tue Dec 06 00:25:35 CST 2016

Total time taken to generate the page: 0.12136 seconds