perform update in select query

[mcnewsxp]

is it possible to update a column within a select query? i need the value of a bit field to set to false if it is null.

tia,
mcnewsxp

[Michael Cole]

Post by mcnewsxp
is it possible to update a column within a select query? i need the value of
a bit field to set to false if it is null.

UPDATE table SET column = 0 WHERE column IS NULL

[Erland Sommarskog]

Post by mcnewsxp
is it possible to update a column within a select query? i need the
value of a bit field to set to false if it is null.

No, but strangely enough you can do the opposite:

UPDATE tbl
SET somecol = 0
OUTPUT inserted.*
WHERE keycol = 1

[mcnewsxp]

Post by Erland Sommarskog

Post by mcnewsxp
is it possible to update a column within a select query? i need the
value of a bit field to set to false if it is null.

UPDATE tbl
SET somecol = 0
OUTPUT inserted.*
WHERE keycol = 1

i appreciate the reply, but i don't fully get it. so i've done my select - then i can do an update on the selected data before sending it out to my app?
i don't understand your code. do i need to pop the selected rows intoa temp table?

[Erland Sommarskog]

Post by mcnewsxp
i appreciate the reply, but i don't fully get it. so i've done my
select - then i can do an update on the selected data before sending it
out to my app? i don't understand your code. do i need to pop the
selected rows intoa temp table?

As I said, you cannot update in a SELECT, but you can return data from an
UPDATE. Not that I really recommend it, I find the feature somewhat bizarre.

If you don't understand the syntax - try it!

[mcnewsxp]

Post by Erland Sommarskog

Post by mcnewsxp
i appreciate the reply, but i don't fully get it. so i've done my
select - then i can do an update on the selected data before sending it
out to my app? i don't understand your code. do i need to pop the
selected rows intoa temp table?

As I said, you cannot update in a SELECT, but you can return data from an
UPDATE. Not that I really recommend it, I find the feature somewhat bizarre.
If you don't understand the syntax - try it!
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

what i need to do is update a bit column from null to zero before the data hits my web pages. but i need to narrow the range of records based on the select where clause of the records i need.

[Erland Sommarskog]

Post by mcnewsxp
what i need to do is update a bit column from null to zero before the
data hits my web pages. but i need to narrow the range of records based
on the select where clause of the records i need.

In the more general case, you will of course need to have an UPDATE
statement and a SELECT statement. UPDATE with OUTPUT will only work for you
in the case you want all data from the same table. If the condition for the
UPDATE and the SELECT are the same, it might be an idea to bounce data over
a temp table, and then use the temp table in the UPDATE.

[mcnewsxp]

Post by Erland Sommarskog

Post by mcnewsxp
what i need to do is update a bit column from null to zero before the
data hits my web pages. but i need to narrow the range of records based
on the select where clause of the records i need.

In the more general case, you will of course need to have an UPDATE
statement and a SELECT statement. UPDATE with OUTPUT will only work for you
in the case you want all data from the same table. If the condition for the
UPDATE and the SELECT are the same, it might be an idea to bounce data over
a temp table, and then use the temp table in the UPDATE.

yes, i finally figured out that is what i'm going to have to do.
thanks,