Altering Data when creating a view

Something like this:
SELECT myColumn1, myColumn2, ..., CASE WHEN location in ('SAT1', 'SAT2') THEN 'SAT' ELSE location END as Location
From myTable
...

yes you can modify data through views, look at this article:-

http://msdn.microsoft.com/en-us/library/ms180800%28v=sql.90%29.aspx

the query can be used like this:-

update yourTable
set Location='SAT'
where Location in ('SAT1','SAT2');

select * from yourTable;

Select allOpen in new window

This works but loads the values into a new column (AS Expr1). I want to update the existing LOCATION column with the new value.

Thanks Buggy, Just looking at your link now. First comment was aimed at rajeevnandanmishra

see this as well:-
http://technet.microsoft.com/en-us/library/ms180800%28v=sql.110%29.aspx

also please modify the above queries to refer to yourView instead of yourTable, my mistake......
;-)

haha thanks Buggy, i was getting slightly confused by 'yourtable' as I did not want to update the underlying tables, just the view data.

Buggy, slight change of plan, I cant change the values in the view as I have group bys. So I could change the values in my SQL SELECT clause that sits in my application and access the view?

Could I have an IIF in my select clause?

SELECT Summin, Summinelse, IIF(LOCATION IN  ('SAT1','SAT2') THEN SAT else LOCATION As LOCATION), MoreFields
FROM X
WHERE X

Yes buggy but

case when location in ('sat1','sat2') then 'sat' else location end as location

adds AS Expr1 to the end and creates a new column, I always wanted to change the values in LOCATION. Ur code suggests it will change the value in LOCATION but it doesnt?

you cannot modify the data through view that contains group by clause, either modify it or use update table....

Well,
A) I cant change the view to have none of the functions that arent allowed.
B) I cannot update the data in the table as other people are working from that table.

So that leaves me with an IIF statement in my SELECT clasue in the front end of my application. Was I along the right lines syntax wise earlier....

SELECT Summin, Summinelse, IIF(LOCATION IN  ('SAT1','SAT2') THEN SAT else LOCATION As LOCATION), MoreFields
FROM X
WHERE X

I have never heard of IIF in sql server, it there in Access but i don't think it's in SQL Server as a statement.

There is a case statement in SQL Server....
see this:-
http://www.sql-server-performance.com/2006/iif-tsql/

Thanks buggy