TROUBLESHOOT: Update Using Nested Select Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TROUBLESHOOT: Update Using Nested Select Statement

Author

Topic

MMMY
Starting Member

14 Posts

Posted - 2012-10-04 : 17:33:37

Hello, I am just trying to understand the logic behind this. Below are two example tables.

TABLE TEAM


NAME	COLOR
Bob	Blue
Jill	Blue
Fred	Red

TABLE REF


PERSON	
Jill

I want to update TEAM so any name that appears in REF is updated to Red.

Here is the query:

UPDATE TEAM
SET COLOR = 'Red'
WHERE NAME IN (
SELECT PEOPLE FROM REF
)

Note that PEOPLE is not a valid column in REF. If you run the nested select query it will bring an error. However, if you run the entire update statement, it will update ALL records to Red.

Can anyone explain the logic behind this?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-04 : 17:42:11

I don't see a People column in either table is that the right query or a typo?

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-04 : 17:45:32

I don't think that it does. I just tried this and got the following error:

Msg 207, Level 16, State 1, Line 11
Invalid column name 'People'.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources