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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.