Update multiple rows using Sub Query

  • stan-617410

    SSC Journeyman

    Points: 76

    Two tables: Users, UserStatus

    Users fields: UserID, Username,Deleted (bit)

    UserStatus: UserID, Username,Status (active,inactive)

    I need to update the "Deleted" field when a user's status is Inactive.

    Update users

    Set deleted = '1'

    Where username IN (select username from UserStatus where status = 'inactive')

    Running this query throws the "Subquery returned more than 1 value." error.

    What am I missing?

    Thanks for your help.

  • Sean Lange

    SSC Guru

    Points: 286536

    Something like this?

    Update users

    Set deleted = '1'

    from users u

    join UserStatus us on u.username = us.username

    where us.status = 'inactive'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Dugi

    SSCoach

    Points: 17998

    stan-617410 (5/13/2011)


    Two tables: Users, UserStatus

    Users fields: UserID, Username,Deleted (bit)

    UserStatus: UserID, Username,Status (active,inactive)

    I need to update the "Deleted" field when a user's status is Inactive.

    Update users

    Set deleted = '1'

    Where username IN (select username from UserStatus where status = 'inactive')

    Running this query throws the "Subquery returned more than 1 value." error.

    What am I missing?

    Thanks for your help.

    I think we need some test data just in case, however the update should be like:

    UPDATE USERS

    SET DELETED = 1

    FROM USERS U INNER JOIN USERSTATUS US ON U.USERNAME = US.USERNAME

    WHERE US.USERSTATUS = 'INACTIVE'

    Even though your script works on my test data ...!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Dugi

    SSCoach

    Points: 17998

    Sean Lange (5/13/2011)


    Something like this?

    Update users

    Set deleted = '1'

    from users u

    join UserStatus us on u.username = us.username

    where us.status = 'inactive'

    Ooops sorry for the same post, I didn't see it while I was writing my post. Ok it has some differences "...INNER..." clause!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    stan-617410 (5/13/2011)


    Running this query throws the "Subquery returned more than 1 value." error.

    What am I missing?

    Thanks for your help.

    You have a trigger on the updated table that can't handle multiple rows at the same time.

  • Sean Lange

    SSC Guru

    Points: 286536

    Dugi (5/13/2011)


    Sean Lange (5/13/2011)


    Something like this?

    Update users

    Set deleted = '1'

    from users u

    join UserStatus us on u.username = us.username

    where us.status = 'inactive'

    Ooops sorry for the same post, I didn't see it while I was writing my post. Ok it has some differences "...INNER..." clause!

    hehe happens to me all the time. Of course we did write exactly the same code, you wrote in ALL CAPS and i wrote in lower case. We both did an inner join I just chose to not use the word inner. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • stan-617410

    SSC Journeyman

    Points: 76

    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    stan-617410 (5/13/2011)


    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

    Need help fixing the trigger? You already have some samples on how to do the joins in the trigger. Let us know if you need help.

  • Dugi

    SSCoach

    Points: 17998

    stan-617410 (5/13/2011)


    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

    As always you are welcome, this time the winner is "Ninja's_RGR" - congrats!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Dugi (5/13/2011)


    stan-617410 (5/13/2011)


    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

    As always you are welcome, this time the winner is "Ninja's_RGR" - congrats!

    THIS time?!?!? :w00t::hehe:;-):-P:-D:-)

  • Dugi

    SSCoach

    Points: 17998

    Ninja's_RGR'us (5/13/2011)


    Dugi (5/13/2011)


    stan-617410 (5/13/2011)


    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

    As always you are welcome, this time the winner is "Ninja's_RGR" - congrats!

    THIS time?!?!? :w00t::hehe:;-):-P:-D:-)

    Noooooooo don't misunderstand ... for all of us you are the SQL Guru guy! - helping lot of people in SSC!

    Keep it up!

    :hehe:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Dugi (5/13/2011)


    Ninja's_RGR'us (5/13/2011)


    Dugi (5/13/2011)


    stan-617410 (5/13/2011)


    Thank you...

    Ninja's_RGR'us, you are right... I have a trigger, I didn't realize that it was having the problem.

    Thanks Dugi, your last submission is where I started.

    I appreciate it.

    As always you are welcome, this time the winner is "Ninja's_RGR" - congrats!

    THIS time?!?!? :w00t::hehe:;-):-P:-D:-)

    Noooooooo don't misunderstand ... for all of us you are the SQL Guru guy! - helping lot of people in SSC!

    Keep it up!

    :hehe:

    Tx, was just 2 easy points to rack up :smooooth: :w00t:

    Only 30 minutes left b4 week-end. So if you still need help, now's the time!

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    2 points if you can guess what RGRus stands for (term not coined by me!).

Viewing 13 posts - 1 through 13 (of 13 total)

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