Hi,
I am using SQL Server 2000. I am not using any Primary Keys in my table. Table Fields:
rollno crseid semester period[/b]
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010 s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
See the 6th and 7th record, you find the duplicate crseid(99790) for the same rollno and the period.
I want to avoid this. Please help me out.Thanks in advance
8 3500 gpl 152
New Member
add a unique index across all your columns
to get rid of the duplication, delete both rows and re-enter one of them
NeoPa 32,556
Recognized Expert Moderator MVP
Use one of the SELECT predicates DISTINCT or DISTINCTROW.
If your query (SQL) started as : - SELECT *
-
FROM [YourTable]
then this would be changed to : - SELECT DISTINCT *
-
FROM [YourTable]
hi,
For SELECT Query Result, we can use DISTINCT or DISTINCTROW. But i dont need this. While doing data manipulation like INSERTING or UPDATING, i wants to stop the duplicate entry. I am not using any keys in my table.
My Table data looks like this, Example: rollno crseid semester period
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010 s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s2 61820 01 JUL-NOV 2010
s2 84280 01 JUL-NOV 2010
s2 61800 01 JUL-NOV 2010
s2 61810 01 JUL-NOV 2010
s2 99830 01 JUL-NOV 2010 s2 99790 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010
see the rollno's s1 and s2, the crseid(99790) having double entry for the rollnos s1 and s2.Please help me out
Hi,
How we can add, Unique index for all the columns.It won't allow more than one row to enter for the same rollno.
My Table data looks like this,
Example:
rollno crseid semester period
s1 61820 01 JUL-NOV 2010
s1 84280 01 JUL-NOV 2010
s1 61800 01 JUL-NOV 2010
s1 61810 01 JUL-NOV 2010
s1 99830 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s1 99790 01 JUL-NOV 2010
s2 61820 01 JUL-NOV 2010
s2 84280 01 JUL-NOV 2010
s2 61800 01 JUL-NOV 2010
s2 61810 01 JUL-NOV 2010
s2 99830 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010
s2 99790 01 JUL-NOV 2010
see the rollno's s1 and s2, the crseid(99790) having double entry for the rollnos s1 and s2.Please help me out
gpl 152
New Member
"How we can add, Unique index for all the columns.It won't allow more than one row to enter for the same rollno."
By deleting the duplicate entries first ... as the whole row is duplicated, you will need to delete both and re-insert just 1 copy of the rows you deleted
Hi,
I know that method, what do you meant to say? But i don't want to do this. Inserting duplicate values and then manually deleting.Its waste. Whether there is an any way to do this using SQL ? Thanks
gpl 152
New Member
Of course its a waste, it should never have happened, your db designer should be shot
Firstly, pull out your duplicates -
Select
-
rollno,
-
crseid,
-
semester,
-
period
-
From mytable
-
Group by
-
rollno,
-
crseid,
-
semester,
-
period
-
Having Count(*) > 1
-
Now delete the duplicates and from the list you saved before, re-insert them
Now apply the unique index and it wont happen again
NeoPa 32,556
Recognized Expert Moderator MVP SanthanaLakshmi:
Inserting duplicate values and then manually deleting.Its waste.
No one ever suggested this.
The suggestion was to delete the duplicate entries (that you've already allowed into your table) first.
When that has been done the server will allow you to set up an unique index across all your fields. This should be done next.
Please, when asking for help, try to do as instructed before turning round to criticise those who are spending their time helping you. If the advice isn't working for you, then the problem is not with the advice.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: atse |
last post by:
Hi,
My table in the database may contain duplicate records, which means except
the primary key (auto_increment) field is unique, all or almost of all the
fields are with the same content. How...
|
by: Ross |
last post by:
i have less hair now than i used to...i have a database with many
duplicate records in it with only one exception, there is one field
with the date it was put in the database and that is different....
|
by: Carroll |
last post by:
I'm looking for a way in SQL to find duplicate records in a single
table, that are the same based on 3 columns, regardless of what is in
the other columns in the duplicate records. I would like to...
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT...
|
by: ramab |
last post by:
Hi. There are two fields in my table that i check so that both of them doesnt occur together again to create a duplicate record. My problem is that if one of the field loses focus then i am able to...
|
by: pbrown |
last post by:
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this:
Name, Location,...
|
by: audj |
last post by:
Hello,
I am trying to use a subquery to avoid duplicate entries when someone submits a form to subscribe to a mailing list.
So I want to check if the email exists before adding the record.
...
|
by: mercea |
last post by:
hi all,
How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks...
|
by: Zwoker |
last post by:
Greetings everyone,
I have a problem that I hope has a simple solution. I am using MS Access 2003.
I have a table that is a list of financial transactions. I am using a make table query over...
|
by: smugcool |
last post by:
HI,
I am having a database in access. Where user imports various data which is in excel format. I want them to stop importing any duplicate record. If any duplicate record is been trying to import...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |