• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 827
  • Last Modified:

MSSQL Delete values from table

I have a table that has 2 columns, one is column_ID and the other column_value. With this query :
(Select column_value FROM table1 group by column_value having COUNT(*)>1)) ,
I find the duplicated column_values and I want to delete the row where the Column_ID is with lower value for that row.

F.eks.
Column_ID    Column_Value
1                           22
2                           22
3                           30
4                           30

In this case I would like to delete the rows with column_ID equal to 1 and 3

How can I do that?
0
gosi75
Asked:
gosi75
1 Solution
 
CrashmanCommented:
Delete
x
from table1 as x inner join
(Select ID, column_value FROM table1 group by column_value having COUNT(*)> 1) as y on x.ID = y.ID
0
 
Scott PletcherSenior DBACommented:
I thin that will delete all rows.


Please try this:

DELETE FROM table1
FROM table1
INNER JOIN (
    SELECT column_value, MAX(column_ID) AS max_column_id
    FROM table1
    GROUP BY column_value
    HAVING COUNT(*) > 1
) AS dups ON
    table1.column_value = dups.column_value AND
    table1.column_id < dups.max_column_id
0
 
gosi75Author Commented:
Great, thanks a lot mate :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now