Solved

Altering Data when creating a view

Posted on 2012-03-28
13
205 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

948 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

21 Experts available now in Live!

Get 1:1 Help Now