Update Column In A View
May 21, 2006
Hello all,
I'd like to create a view which shows all specific column values
(null values for example)
as calculated values from another column,
if for example i have the table:
---------------
| col1 | col2 |
|------|------|
| 123 | null |
| 126 | 9 |
---------------
i would like the view to be:
---------------
| col1 | col2 |
|------|------|
| 123 | 6 | ----> 6, since it's the sum of col1 digits 1+2+3
| 126 | 9 |
---------------
I don't really know how to do that, although i'm quite sure it's possible, any help will be appreciated,
Best Regards,
pitt
View 3 Replies
ADVERTISEMENT
Jan 9, 2008
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
View 5 Replies
View Related
Sep 26, 2007
Please help.
give you example
I have table = Product
and column = Product_name varchar(20)
Then I have view call v_product this view select * from product.
Each time I modify product type become varchar(25). My view v_product didn't get change. I have to alter the view
The question I have. I have a lot of tables more then 80 need to change the data type. Any one have best way refesh the view??? Thank you
View 11 Replies
View Related
Aug 24, 2007
hi
i have one view name vtest .which contains records from two table .i want to update this view in sqlserver 2000
how we can do this?
thanx
View 8 Replies
View Related
Jul 19, 2006
Hi all ,
I have a problem to insert new data to a view which created from 2 tables.
I am trying to so from run time
Any help will be appreciated
View 3 Replies
View Related
Mar 26, 2014
I am going to create a custom view (SQL08R2 now, but in SQL2012 later this year). One unusual feature is that someone suggested to limit the update capability to one column, and only allow certain number of rows to update, for instance in dev stage, 1 row at a time, and change it to allow 5 rows at a time when in production.
Could you build a trigger on a view? What other options out here?
View 2 Replies
View Related
Aug 15, 2014
I create a view with 2 joined tables:
with cte as (
select
DataID,
Name,
SubType,
FileType,
MimeType,
VersionID,
Version
from dtree A1, dversdata A2
where A1.dataid=A2.Docid And A1.Subtype='144'
AND
A2.mimetype='application/news-message-id'
update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
View 1 Replies
View Related
Mar 14, 2008
How can we use Update Properties on View? How update properties deals with multiple tables in view.
View 1 Replies
View Related
Mar 19, 2008
I have a flat /derived table of orders that customers made, virtually
order_tbl
(
order_id
,item_id
,product_id
,product_price
,product_name
,customer_id
,customer_name
,order_price
,order_tax
,order_date
,order_year
,order_month
,order_day
)
and I would like to have:
1.update on this derived table daily (how/what should I do - with triggers/jobs against the original tables?)
2.create a view that would show:
- by years the percentage of sold products
- quarterly percentage of new customers / rebuyers / etc.
- ...
3. Thinking of making a cube in BIDS (with dimensions / measures)
Any help is appreciated. :-)
View 1 Replies
View Related
Sep 25, 2006
Hi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
View 1 Replies
View Related
Feb 27, 2008
hiii, i am using asp.net 2005 and sql server 2005...i hav a page in which i have used gridview and sql data source..i have written the update command for the same..the problem is when i add the where clause in the query i get an error ,,,,here is the code
UpdateCommand="UPDATE SMEtre_Master SET FirstName =@FirstName, LastName =@LastName, Type_of_SME =@Type_of_SME, Agency_Name =@Agency_Name, Email =@Email, Address =@Address, Phone =@Phone, Mobile =@Mobile, Fax =@Fax, Experience =@Experience, City =@City, State =@State where SME_Id=@SME_Id"
View 3 Replies
View Related
Feb 23, 2006
I put a grid view on a web form ,when I run it -the SELECT, EDIT works
the UPDATE,DELETE makes an error although I use the sama data,I added the error :
Anyone can help?
Server Error in '/CrystalReportsWebSite1' Application.
The data types text and nvarchar are incompatible in the equal to operator.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
View 2 Replies
View Related
Jul 19, 2013
I want to update a data in grid view
I have three columns in grid view
First two column are shift id and date these two column are taken from table 1
The third column is shift column this column are taken from table 2
Now I want to update a data where shift id and shift column are same
table 2 have these column
shift
shiftid
and table 1 has these column
shift id,
date
View 5 Replies
View Related
Mar 26, 2008
I have a view based on two tables. Now I want to update that view in such a manner that the columns of both the tables are going to update.
Can you suggest me what code I should write so that I can update that view.
View 1 Replies
View Related
Jan 11, 2008
Hello all...I'd appreciate any help on this one.
I created a View...the view looks at four seperate tables.
Next, I created an Instead of Update trigger on that view. It works fine...for a regular UPDATE...SET.
However, it throws an error when I try an update and self join based on that view:
Update T1
Set RateUsed = T1.RateUsed
From Taxroll..Taxroll T1 Join
Taxroll..Taxroll T2 on
T1.Asmt = T2.Asmt and
T1.Taxyear = T2.Taxyear
Where T1.Asmt = '123456789012'
And T1.Taxyear = 2007
And T2.RollChgNum = ''
And T1.RollChgNum Like '%X'
And IsNull(T1.RateUsed,'') > ''
Msg 414, Level 16, State 1, Line 2
UPDATE is not allowed because the statement updates view "Taxroll..Taxroll" which participates in a join and has an INSTEAD OF UPDATE trigger.
There are a few caveats:
First, I thought the join issue was in the view itself. I re-created the view using no joins...all subqueries and still get the error. Second, I re-created the Instead of Update Trigger with no joins, and still get the error.
Thanks!
View 1 Replies
View Related
Oct 18, 2005
Hi,I have an application that's running fine on development servers (weband database-sql server 2000). I'm updating a record through a thirdparty component but I don't think the component is the problem. What'shappening is that I'm updating fields that are part of view. I'm onlyupdating fields in one table of the view and this works fine in thedevelopment environment.What happens in the production environment when I try to update(using the third party component) I get the following message:"Current recordset does not support updating. This may be a limitationof the provider or of the selected locktype."As an experiment I took the same code but removed the view, leavingonly the table I want to update as the record source. In that case theupdate worked. So it seems that something in the production databasedoesn't like me updating a view. However I can do that in the databasein the development environment.The third party component is dbnetgrid which works fine in thedevelopment environment. I can only conclude it's something about thedatabase that prevents me from updating this same table if it's in aview. I've talked to our DBA but he says there's no difference betweenthe databases. Any ideas would be appreciated.Neil
View 2 Replies
View Related
Jul 20, 2005
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies
View Related
Dec 27, 2007
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
View 4 Replies
View Related
Sep 9, 2015
I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?
View 4 Replies
View Related
Aug 27, 2015
How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies
View Related
Apr 5, 2007
I am creating a website.
i have completed the GUI. i have labels on the left side of the form and a text box for each of those labels on the right side of the form. i have the two dropdown boxes that you have helped me create and bind. now im stuck on how to start the code to get this all working. i know what i need it to do (pseudocode) but dont know how to write the code for it. these books and online sources, really dont say where to put things, they just give examples, not stating where to place it.
I am using a SQL database called Manufacturers
i would like to insert or update, but not delete records wtih this tool
pixelsyndicate mentioned using the GUI to do the code for me, would love to know how to do this.
any help would be awesome, or any good beginner resources would help too
Thanks
View 1 Replies
View Related
Feb 15, 2005
Hi all,
I have two tables (staging and Cdate) and neither objects has any constraints.
staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
Anyone can help to fix it? Thank you for the help!
update s
set s.date=c.date
FROM cdate c join staging s on(s.id=c.id)
Where s.date is null and id=2
View 3 Replies
View Related
Dec 22, 2005
Hello all,SQL Server 2000 documentationhttp://www.microsoft.com/technet/pr...rt10/c3761.mspxstates that if view is using "NOT NULL" columns of a base table, theninsert/update performed on a view must provide dummy values for thosecolumns, and code of the trigger should ignore them.But I cannot reproduce this restriction. Code below pasted to QueryAnalysershows that I can not supply dummy values for "NOT NULL" fields when I updateview and still have update done. What do I miss ?VT/*--setup step 1. execute only inside of this commentSET NOCOUNT ONCREATE TABLE TestTable(keyField INT IDENTITY(1,1),dataField1 INT NOT NULL,dataField2 INT DEFAULT 1 NOT NULL)*//* --setup step 2. execute only inside of this commentCREATE VIEW TestView ASSELECT * FROM TestTable*//*--setup step 3. execute only inside of this commentCREATE TRIGGER TestViewTrig_IUON dbo.TestViewINSTEAD OF UPDATEASBEGINSET NOCOUNT ONUPDATE TestTableSETDataField1 = inserted.DataField1,DataField2 = inserted.DataField2FROMTestTable ttINNER JOINinsertedON inserted.KeyField = tt.KeyFieldEND*//*--setup step 4. execute only inside of this commentINSERT INTO TestTable (DataField1,DataField2) Values (1,2)INSERT INTO TestTable (DataField1,DataField2) Values (3,4)INSERT INTO TestTable (DataField1,DataField2) Values (5,6)*/SELECT * FROM TestView-- SQL Server lets me not specify DataField2 when update DataField1 orreverse,-- which is opposed to what documentation saysUPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3SELECT * FROM TestView/*-- remove test environmentDROP VIEW TestViewDROP TABLE TestTable*/
View 3 Replies
View Related
Aug 21, 2006
hopefully, someone has already solved this problem
I have been assigned an application that maps data from access databases into a view in Sql Server 2005. A vb6 application does a select on the view. The recordset is then modified by the application, and eventually, the original tables will be updated with the new data. Some of the fields in the view are the result of calculations ( i.e. adding multiple varchar fields together to produce one complex varchar result ). Any of the fields that are calculated in the manner previously described have the adFldUnknownUpdatable bit set in the attributes of the recordset, and consequently, you get a -2147217887 error ( multiple-step operation .... ) when you try to change the value of the field in the recordset. Does anyone know how to get around this problem?
View 1 Replies
View Related
Nov 16, 2007
For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK.
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList.
I am wondering how can I add an identity PK to my view?
TIA,Jeffrey
View 1 Replies
View Related
Mar 11, 2006
I have a SQL table that consists of columns A, B and C. I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column. This computed column has the following logic:
If B is blank or null then NewColumn = A + ' - ' C
else
NewColumn = A + ' - ' + B
I am just wondering how the SQL statement should look like....
View 2 Replies
View Related
Sep 17, 2001
Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.
TIA
Jennifer
View 2 Replies
View Related
Jun 11, 2003
I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:
View
====
Col1: Unit = get_number_of_units()
Col2: MV = get_number_of_units() * get_unit_value()
I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?
Is get_unit_value() being called if I do Select Col1 from View?
Thanks.
View 1 Replies
View Related
Oct 25, 2006
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.
View 2 Replies
View Related
Apr 21, 2008
*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1
as
select tab.col1,tab.col2 from
from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
thanx
View 2 Replies
View Related
Jan 31, 2014
I'm using SQL Server 2008 to solve the following exercise.
I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone
I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders
View 4 Replies
View Related
Aug 14, 2014
how can i reference a column in a view.
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
View 2 Replies
View Related
