none
update results from a select count

    Question

  • I run the followin select to see if I have duplicate fields and I count them.

    I then take these results and update the dups to a NULL value but I perform the update manually to each record using the update statement.

    How can I perform an update to the results from this query without going one by one?

    SELECT field1, COUNT(*)

    FROM tableName

    GROUP BY field1

    HAVING (COUNT(field1) > 1)

    Tuesday, November 20, 2012 5:47 PM

Answers


  • ;WITH mycte AS (SELECT field1,ROW_NUMBER() OVER( partition BY field1 ORDER BY NEWID() ) rn FROM tableName) UPDATE mycte SET field1 = NULL WHERE rn > 1 SELECT * FROM tableName


    • Proposed as answer by Naomi NModerator Tuesday, November 20, 2012 6:23 PM
    • Marked as answer by Unwind_1 Tuesday, November 20, 2012 6:56 PM
    Tuesday, November 20, 2012 6:04 PM
    Moderator
  • In this case we may want to use ROW_NUMBER() solution, e.g.

    ;with cte as (select *, row_number() over (partition by FieldName order by SomeOtherField) as Rn from myTableWithDups)

    update cte set FieldName = NULL where Rn > 1

    select * from myTableWithUpds -- to verify the result


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 20, 2012 6:24 PM
    Moderator

All replies

  • Look at the UPDATE from GROUP BY example at the following blog:

    http://www.sqlusa.com/bestpractices2005/updatewithgroupby/

    Basically you make the GROUP BY query into a derived table and JOIN it with other table(s).


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, November 20, 2012 5:55 PM
    Moderator
  • Can you please clarify what you're trying to achieve? Do you want to mark all dups as NULL or all dups but one? Can you provide your query, DDL of the table, some insert statements and desired output based on the input?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 20, 2012 5:57 PM
    Moderator

  • ;WITH mycte AS (SELECT field1,ROW_NUMBER() OVER( partition BY field1 ORDER BY NEWID() ) rn FROM tableName) UPDATE mycte SET field1 = NULL WHERE rn > 1 SELECT * FROM tableName


    • Proposed as answer by Naomi NModerator Tuesday, November 20, 2012 6:23 PM
    • Marked as answer by Unwind_1 Tuesday, November 20, 2012 6:56 PM
    Tuesday, November 20, 2012 6:04 PM
    Moderator
  • All, but one - I want to update all dups but one from each group.

    Hope that helps.

    Tuesday, November 20, 2012 6:17 PM
  • Make it clear which field of table you wanted to update .

    If your query is how can you want to update .....having clause then see below example

    UPDATE OrderDelivery
    SET ConfirmedDeliveryDate = '2004-04-18'
    GROUP BY LineNumber, PurchOrderNo
    HAVING COUNT(LineNumber) = 1
    AND PurchOrderNo = '0000041281'

    Tuesday, November 20, 2012 6:23 PM
  • In this case we may want to use ROW_NUMBER() solution, e.g.

    ;with cte as (select *, row_number() over (partition by FieldName order by SomeOtherField) as Rn from myTableWithDups)

    update cte set FieldName = NULL where Rn > 1

    select * from myTableWithUpds -- to verify the result


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 20, 2012 6:24 PM
    Moderator
  • this is really good and it does work. How can I understand how it's working, is there anyting I can reference?

    Thanks for your help

    Tuesday, November 20, 2012 6:38 PM
  • Brad Schulz has a good blog about CTE that I believe explains that cte can be used for update. See

    CTE: Coolest T-SQL Enhancement


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 20, 2012 6:55 PM
    Moderator
  • in the above code

    just before update, use select * from mycte to get the new result set. In that you can see an additional column rn, which is a counter for each group. so in update cte you are updating rows whose rn values is <> 1


    • Edited by Johnson T A Tuesday, November 20, 2012 6:58 PM
    Tuesday, November 20, 2012 6:57 PM