| Author |
 Topic  |
|
|
Nathan
Help Moderator
USA
7664 Posts |
 Posted - 19 February 2002 : 20:36:57
|
I have a table with three fields. One is an ID (TABLE_ID), the other two are IDs from other tables (MEMBER_ID) and (FORUM_ID). . . so this table is serving to tie records from the other two tables together.
I need to move some of the ties from one forum to another, so I will be updateing the FORUM_ID fields here. I need to know if there is any way to prevent double records from occuring. (The tables_IDs would be different but the member_id and forum_id would be the same)
Anyone know how to prevent this?
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 February 2002 : 20:41:10
|
quote:
I have a table with three fields. One is an ID (TABLE_ID), the other two are IDs from other tables (MEMBER_ID) and (FORUM_ID). . . so this table is serving to tie records from the other two tables together.
I need to move some of the ties from one forum to another, so I will be updateing the FORUM_ID fields here. I need to know if there is any way to prevent double records from occuring. (The tables_IDs would be different but the member_id and forum_id would be the same)
Anyone know how to prevent this?
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource
Creating a unique index based on those two columns?! I guess that's possible. What DBMS will you be using?
|
 |
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 19 February 2002 : 21:25:56
|
There is a unique index already in the table, but that doesn't matter because its not the index I'm conserned about. For example say this is my table.
TABLE_ID | FORUM_ID | MEMBER_ID
1 2 6
2 3 1
3 5 2
4 4 6
5 1 3
6 1 7
7 2 2
8 5 3
9 3 5
20 1 6
Now, lets say I want to change all FORUM_IDs that are 2 to 4. . . so I do it and I end up with a duplicate record as shown in red.
TABLE_ID | FORUM_ID | MEMBER_ID
1 4 6
2 3 1
3 5 2
4 4 6
5 1 3
6 1 7
7 4 2
8 5 3
9 3 5
20 1 6
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 February 2002 : 21:30:21
|
You can have more than one unique index as long as they are not identical to other existing indexes.
You can create a unique index based on the FORUM_ID and MEMBER_ID columns. I'm not talking about another primary key here. Just another unique index...
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 February 2002 : 21:36:16
|
Nathan,
If you are using Access the SQL would go like this:
CREATE UNIQUE INDEX myIndexName ON myTableName (FORUM_ID,MEMBER_ID) WITH DISALLOW NULL;
Just write in the QBE, in SQL view, execute it and your index is created...
|
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 19 February 2002 : 21:40:20
|
I'm working with existing tables that I cant change, and if I have duplicats as shown above, well, thats bad. Even if it wont cause an SQL error.
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 February 2002 : 21:44:19
|
quote:
I'm working with existing tables that I cant change, and if I have duplicats as shown above, well, thats bad. Even if it wont cause an SQL error.
Nathan Bales - Romans 15:13
---------------------------------
Snitz Exchange | Mod Resource
Sorry I got lost here. You mean you can't add the index? It won't cause any other change on the table, although the creation may fail if some record violates the uniqueness of the index.
If you create the index it won't be possible to add a record that violates its uniqueness...
|
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 19 February 2002 : 21:45:01
|
Would something like this work?
UPDATE myTable SET FORUM_ID = 4
WHERE FORUM_ID = 2 AND MEMBER_ID NOT IN
(SELECT MEMBER_ID FROM myTable WHERE FORUM_ID = 4)
Nikkol |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 February 2002 : 21:48:57
|
quote:
Would something like this work?
UPDATE myTable SET FORUM_ID = 4
WHERE FORUM_ID = 2 AND MEMBER_ID NOT IN
(SELECT MEMBER_ID FROM myTable WHERE FORUM_ID = 4)
Nikkol
Sorry for the multiple corrections to this. Something of the sort could be used, I guess.
You'd have to run such a query for each record though. I guess it could be possible to create it, using the QBE if the DBMS is Access, in such a way that you could just run it once and update all the records.
Edited by - ruirib on 19 February 2002 22:03:52 |
|
|
|
xMANIGHTx
Junior Member
Italy
191 Posts |
Posted - 20 February 2002 : 08:37:16
|
quote:
Would something like this work?
UPDATE myTable SET FORUM_ID = 4
WHERE FORUM_ID = 2 AND MEMBER_ID NOT IN
(SELECT MEMBER_ID FROM myTable WHERE FORUM_ID = 4)
Nikkol
This should work pretty fine... just add AND TABLE_ID = n if you don't want to change alle the FORUM_ID from 2 to 4 for each different member)
 Distractly yours... manight@audiopro.it |
|
|
| |
Topic |
|
Topic Locked
