Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
update results from a select count

Answered update results from a select count

  • Tuesday, November 20, 2012 5:47 PM
     
     
    Sign In to Vote
    0

    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)

     

All Replies

  • Tuesday, November 20, 2012 5:55 PM
    Moderator
     
     
    Sign In to Vote
    0

    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:57 PM
    Moderator
     
     
    Sign In to Vote
    0
    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 6:04 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0
     

    ;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

     
  • Tuesday, November 20, 2012 6:17 PM
     
     
    Sign In to Vote
    0

    All, but one - I want to update all dups but one from each group.

    Hope that helps.

     
  • Tuesday, November 20, 2012 6:23 PM
     
     
    Sign In to Vote
    0

    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:24 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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:38 PM
     
     
    Sign In to Vote
    0

    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:55 PM
    Moderator
     
     
    Sign In to Vote
    0

    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:57 PM
     
     
    Sign In to Vote
    0

    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
    •