How to find duplicate record

[yogs20]

Hi,
I have a table where i have 'Order No' and 'Order Recd No'. 'Order No' may appears many times, I want the 'Order Recd No' should come only once against the 'Order No'. please tell me the SQL statement to find the 'Order Recd No' number entered is already exsists in the table or not. please help me, I have just started the SQL. i am not at all good in SQL...
thanx.

 

[ClaireHCM]

If you are in the creating table step~~

create table d(col1 int,col2 int unique)
or
create table d(col1 int,col2 int primary key)

If already you have created table with values inserted.(But you need to make sure there is no duplicate row existing in the 'Order Recd No' column,because by executing the following query can only ensure that no duplicated row is allowed to be inserted in the future!)

alter table d add unique(col1)
or
alter table d alter column col1 int not null
alter table d add primary key(col1)

(If choosing primary key you need to specify that column is not nullable!)

 

[yogs20]

Dear ClaireHsu

Thank u very much,
actually i hav created a table and a form . it works like this,
i have placed an order with A, (order no = 2000)for qnty 200pics i will receive only 50 pics first time against this order (2000) vide order recd no say 3456. agian i will receive 50 pics against the same order (2000) vide order recd no 3460. my query is i want to find out is this no (3460) is already entered or not. in table (mdb) i have marked this field as duplicate not allowed, and order no as duplicate allowed. i am not updating the table i want to insert into the table. please suggest me with SQL statement to be fired at lost focus to find out is the number entered into this text box is already exisit in the table or not.

thank you very much again.

 

[ClaireHCM]

Do you want to execute a stored procedure?User defined function?Or Trigger(least choice I think)

If you just want to see whether the rec no which you are going to insert existed in the table or not.Actually you can do a select statement.

select * from table where [Order Recd No] = the number you want to insert.

If you want to run it as a stored procedure.

create proc checkrecno @recno int
as

if exists(select * from table where [Order Recd No]=@recno)
print 'Already exist'
else
print 'Not exist'

--Usage
--Exec checkrecno 3460

 

[SonOfEmidec1100]

There are many ways, here is just one. I would probably put it in a stored proc and pass it the 2 values and instead of the print statements either return an ok/notok value or do the actual processing required.

DECLARE @OrderRecNo INT,@OrderNo INT
--Ive assumed the data types are int.
SET @OrderNo = 2 --your required values
SET @OrderRecNo = 1 --

IF EXISTS (SELECT OrderNo FROM _MyTable1 WHERE OrderNo = @OrderNo AND OrderRecNo = @OrderRecNo)
BEGIN
PRINT 'Already used'
END
ELSE
BEGIN
PRINT 'Im free'
END

Hope this helps get you on your way...

 

[SonOfEmidec1100]

Must be a good idea.

 

[hoggle]

here's a query to get all the duplicate records from a single table
=======================================================
SELECT
OrderNo, OrderRecdNo
FROM
--Table with duplicates
Orders
WHERE
EXISTS (
SELECT
NULL
FROM
--Table with duplicates, with an alias
Orders b
WHERE
--Join each field with *itself*. and any other where
--clause
b.[OrderNo] = Orders.[OrderNo]
AND b.[OrderRecdNo] = Orders.[OrderRecdNo]

--if you need certain records do it here
AND OrderRecdNo not in ('0017072') --(ect.)

GROUP BY
--GROUP BY all fields in output because of the HAVING
b.[OrderNo], b.[OrderRecdNo
HAVING
--control output from here
count(*) > 1
)
ORDER BY OrderNo, OrderRecdNo