Update multiple rows using Sub Query

Question

Post reply

Update multiple rows using Sub Query

stan-617410

SSC Journeyman

Points: 86
More actions
May 13, 2011 at 9:55 am

#389525

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.

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

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

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 286589 More actions · Answer 5

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: 86 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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