Using result set to UPDATE - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using result set to UPDATE

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-01-17 : 19:32:09

Ok, I have a query that returns about 160 rows and looks something like the following:
personID,
PersonName,
State,
LevelID

Now I need to set the every LevelID to the value 1. Only problem is I just need to update those 160 rows returned from the query. Is that possible or do rows always need to be updated via tables and such?
--Nick

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-17 : 19:53:32

your query

select personID,
PersonName,
State,
LevelID
from ......

update tbl
set LevelID = 1
from tbl t1
join (select personID,
PersonName,
State,
LevelID
from ......
) t2
on t1.personID = t2.personID
and t1.LevelID = t2.LevelID

depends a bit on what the PK is.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-01-17 : 20:05:38

PersonID is the PK. But yeah I think that'll work, cool. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-17 : 20:12:24

In that case
update tbl
set LevelID = 1
where personID in
(select personID
from ......
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources