Post reply

How to delete a record with different column values?

  • May 17, 2013 at 10:06 am

    #272821

    I have a table where the unique identifier is studentId.

    Now this table contains decision column

    If decision has been made, then decision=’yes’/’No’

    If decision hasn’t been made decision=’none’

    Now the tables gets appended when the decision has been made, so for a student, we get 2 rows one, with decision=’none’ and other with decision=’yes’/’No’

    I just want to delete the row for decision =’none’ when there is a yes/no decision.

    For each row there is a unique DecId.

    So for 2 rows with same emplid, we have 2 different DecId.

    How do I do that?

    Thanks,

    Blyzzard

  • May 17, 2013 at 10:12 am

    #1616249

    if DecId is an identity() column, can can infer that if the data was ordered by DecId, only the last record applies;

    Since this looks a lot like homework, I'll give you a general answer

    i'd use the row_number function and make sure i use the partition by statement, and order by the DecId;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 17, 2013 at 10:13 am

    #1616250

    amar_kaur16 (5/17/2013)

    I have a table where the unique identifier is studentId.

    Now this table contains decision column

    If decision has been made, then decision=’yes’/’No’

    If decision hasn’t been made decision=’none’

    Now the tables gets appended when the decision has been made, so for a student, we get 2 rows one, with decision=’none’ and other with decision=’yes’/’No’

    I just want to delete the row for decision =’none’ when there is a yes/no decision.

    For each row there is a unique DecId.

    So for 2 rows with same emplid, we have 2 different DecId.

    How do I do that?

    Thanks,

    Blyzzard

    Like this (untested as you didn't give us anything to work with):

    with BaseData as (

    select

    StudentId,

    Decision, -- ('Yes','No', or 'None' for this example)

    rn = row_number() over (partition by StudentId order by case when Decision = 'None' then 1 else 0 end asc)

    from

    dbo.MyTable -- Don't know your table name

    )

    delete from BaseData where rn > 1;

  • May 17, 2013 at 12:27 pm

    #1616320

    Thank You Lynn.

    It worked.

  • May 22, 2013 at 4:00 am

    #1617335

    you can use row number with partitions to do the same

  • May 22, 2013 at 4:13 am

    #1617341

    subhajeetsur (5/22/2013)

    you can use row number with partitions to do the same

    I think that is exactly what Lynn has suggested above.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply