Solved

Altering Data when creating a view

Posted on 2012-03-28
13
204 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:deanlee17
  • 7
  • 5
13 Comments
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37775578
Something like this:
SELECT myColumn1, myColumn2, ..., CASE WHEN location in ('SAT1', 'SAT2') THEN 'SAT' ELSE location END as Location
From myTable
...
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37775585
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
 

Author Comment

by:deanlee17
ID: 37775592
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
 

Author Comment

by:deanlee17
ID: 37775598
Thanks Buggy, Just looking at your link now. First comment was aimed at rajeevnandanmishra
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37775604
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
 

Author Comment

by:deanlee17
ID: 37775610
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:deanlee17
ID: 37775629
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
 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37775669
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
 

Author Comment

by:deanlee17
ID: 37775820
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
 
LVL 20

Expert Comment

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

Author Comment

by:deanlee17
ID: 37775837
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
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37775854
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
 

Author Comment

by:deanlee17
ID: 37775965
Thanks buggy
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now