Altering Data when creating a view

Hi experst,

Im creating a view, In my SELECT I have a column called LOCATION, if the location is SAT1 or SAT2 then I want them renamed to SAT. I do not want this in a new column, I want it to alter the existing data in the LOCATION column, is this actually possible?

Thanks,
Dean.
deanlee17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajeevnandanmishraCommented:
Something like this:
SELECT myColumn1, myColumn2, ..., CASE WHEN location in ('SAT1', 'SAT2') THEN 'SAT' ELSE location END as Location
From myTable
...
0
BuggyCoderCommented:
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;

Open in new window

0
deanlee17Author Commented:
This works but loads the values into a new column (AS Expr1). I want to update the existing LOCATION column with the new value.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

deanlee17Author Commented:
Thanks Buggy, Just looking at your link now. First comment was aimed at rajeevnandanmishra
0
BuggyCoderCommented:
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......
;-)
0
deanlee17Author Commented:
haha thanks Buggy, i was getting slightly confused by 'yourtable' as I did not want to update the underlying tables, just the view data.
0
deanlee17Author Commented:
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
0
BuggyCoderCommented:
yes, but then your question was to primarily modify the view....
If you can do this, it will be fine, even Case statement can also help you in case you don't want to use iff

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanlee17Author Commented:
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?
0
BuggyCoderCommented:
you cannot modify the data through view that contains group by clause, either modify it or use update table....
0
deanlee17Author Commented:
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
0
BuggyCoderCommented:
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/
0
deanlee17Author Commented:
Thanks buggy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.