Click here to Skip to main content
15,445,997 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     CRLF
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 ;)
 
Share this answer
 
Comments
[no name] 26-Aug-12 17:07pm     CRLF
Good answer. +5
Maciej Los 26-Aug-12 17:12pm    
Thank you, Meysam ;)
[no name] 27-Aug-12 10:15am     CRLF
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
 
Share this answer
 
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     CRLF
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     CRLF
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