Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Altering Data when creating a view

Posted on 2012-03-28
13
Medium Priority
?
212 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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
 

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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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