Click here to Skip to main content
15,041,548 members
Please Sign up or sign in to vote.
2.00/5 (4 votes)
I have a table
Column Name         Value
1                    20
1                    30


The I am Executing one update query
This make my table like this

Column Name         Value
1                    50
1                    50


Now, I want to delete one record from them, How can I delete that?
Only one record from the two record should exist in the table after deleting.
Please please help me.


can anyone tell me rownumber() will work or not for this

and Please don't ask me how or why I created this ridiculous table structure.
because the table structure is not what is visible to you, the table contains 15 columns. Depending to different forms in C#.
But this two column got the first value. More stories are there.
That is not the problem.Please don't refer other 13 columns of the table.
Please answer me the question, which I have mentioned.

Please help me.
Posted
Updated 26-Aug-12 10:24am
v2
Comments
[no name] 26-Aug-12 16:15pm
   
whose that?
you don't know the answer and decrease my points,
gr8
may I know the name?

I'm not sure what you want to do...
If you wnat to show duplicates, use something like this:
SQL
SELECT ID, COUNT(ID) AS CountOfID
FROM Table1
GROUP BY ID
HAVING COUNT(ID)>1


More at: HAVING[^]

To remove duplicates for ID = 1, use:
SQL
DELETE DT.*
FROM (SELECT ID, COUNT(ID) AS CountOfID
    FROM Table1
    GROUP BY ID
    HAVING COUNT(ID)>1) AS DT
WHERE DT.ID=1


It should works for you ;)
   
Comments
[no name] 26-Aug-12 17:07pm
   
Good answer.
+5
Maciej Los 26-Aug-12 17:12pm
   
Thank you, Meysam ;)
[no name] 27-Aug-12 10:15am
   
I already solved the problem without your kind help.
But the query is actually good.
But the problem is solved.
You are too slow in answering, that is why I will not give you points
nor select your answer.
BTW thanks.
Maciej Los 27-Aug-12 11:41am
   
You are welcome ;)
SQL
DELETE TOP(1) FROM table;


this is the solve
   
Comments
Maciej Los 26-Aug-12 16:58pm
   
This is not correct answer. This query deletes only one record (first record) from table. I'll give you a chance to answer good solution. That's way i'm not down-voting.
[no name] 27-Aug-12 10:19am
   
I used some where criteria and already solve my problem using that query.
I can calculate number of rows(Such as n)
and can delete using
DELETE TOP(n-1) FROM table

ok?
Thanks
Maciej Los 27-Aug-12 11:39am
   
If you found an answer, share your solution...
As i wrote before, this is answer is not correct!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900