UPDATE with nested select - SQL Server Forums

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

UPDATE with nested select

Author

Topic

bdhd
Starting Member

2 Posts

Posted - 2011-01-18 : 18:45:15

New to SQL, and having done a lot of searching, I hope to find some help...

--------------------
|ID | FieldID | FieldValue
--------------------
| 1 | 100 | Alpha
--------------------
| 1 | 101 |
--------------------
| 2 | 100 | Beta
--------------------
| 2 | 101 |
--------------------
| 3 | 100 | Alpha
--------------------
| 3 | 101 |
--------------------

In this SimpleTable example, I would like to SET XXX into FieldValue WHERE the value of FieldID =101, but only IF the value of FieldValue ='Alpha' WHERE FieldID =100

So, in this example, FieldValue for FieldID 101 would be XXX for ID 1 and 3. ID 2 would remain blank.

Seems simple, but when I write it, it doesn't work out...

UPDATE SimpleTable
set FieldValue ='XXX'
where FieldID =101
and
FieldValue
In (SELECT FieldValue FROM SimpleTable
WHERE FieldValue = 'Alpha')

Any help would be very much appreciated. Otherwise, I'll be doing a SET by ID for about 300 products... One at a time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-18 : 20:07:35

[code]
update s
set fieldvalue = 'XXX'
from simpletable s
where fieldid = 101
and exists
(
select *
from simpletable x
where x.id = s.id
and x.fieldvalue = 'Alpha'
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bdhd
Starting Member

2 Posts

Posted - 2011-01-18 : 23:07:37

Perfect!

Thanks so much for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources