locked
how to create view that has an update statement RRS feed

  • Question

  • Tools: MS Management Studio SQL 2008 R2
    Code:

    Create View as Mocha
    (
    UPDATE  dbo.Cola 
    SET Login_ID = Replace(PE15,RTRIM(Cast(Login_ID),'') 
    
    UPDATE dbo.cola 
    SET  P4 = NullIf(P4,'')
    
    )
    
    

    I would like to create a database object "View" to hand it over to a user and run the script. How I would create a a view that would accept an update statement. Are any work around to accomplish this goal

    Thursday, October 30, 2014 8:33 PM

Answers

  • Tools: MS Management Studio SQL 2008 R2
    Code:

    Create View as Mocha
    (
    UPDATE  dbo.Cola 
    SET Login_ID = Replace(PE15,RTRIM(Cast(Login_ID),'') 
    
    UPDATE dbo.cola 
    SET  P4 = NullIf(P4,'')
    
    )
    

    I would like to create a database object "View" to hand it over to a user and run the script. How I would create a a view that would accept an update statement. Are any work around to accomplish this goal

    Hi,

    1. Use SP instead of view, to do update to the tables
    2. Within SP, create the relevant view, even by dynamic Sql.

    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Kalman Toth Friday, October 31, 2014 6:46 AM
    • Marked as answer by Sandra VO Friday, October 31, 2014 4:05 PM
    Friday, October 31, 2014 5:24 AM

All replies

  • You can't.  I have no idea what an alternative would be since your goal seems to be rather specific and does not lend itself to a generic query that "reads" data.  Perhaps a computed column is what you need?
    Thursday, October 30, 2014 8:44 PM
  • A computed column cannot be the target of an INSERT or UPDATE statement.
    Thursday, October 30, 2014 8:49 PM
  • You're quite correct, it can however can be set to the output of a function (or nested functions).

    CREATE TABLE #cola (login_ID VARCHAR(20), PE15 VARCHAR(20))
    INSERT INTO #cola (login_ID, PE15) VALUES ('abcde123','abcde')
    
    ALTER TABLE #cola
       ADD NewLoginID AS REPLACE(PE15,RTRIM(CAST(Login_ID AS VARCHAR(10))),'')
    
    SELECT *
      FROM #cola
    
    DROP TABLE #cola

    Thursday, October 30, 2014 8:59 PM
  • good approach but I am afraid this technique not applicable for my case because SQL wont allow to change the table definition on data that already exists. I'll have to drop the entire data then build the table from the ground up which create more administrative work. 
    Thursday, October 30, 2014 9:37 PM
  • It won't if you use table designer in SSMS, but I'd bet you a beer the TSQL statement works :)

    Also:

    SELECT *
     INTO cloneTable
      FROM existingTable
     WHERE 'this' = 'that'
    
     ALTER TABLE cloneTable
     ADD someColumn VARCHAR(10)
    
     INSERT INTO cloneTable
     SELECT *
       FROM existingTable

    Boom!

    Thursday, October 30, 2014 10:19 PM
  • I like your sense humor but the the existing table is used as a backbone for an application and there are referential data integrity.   My orignal code has over 100 code lines that does data cleansing using update and table expression and I want to deploy this solution in a one line of code or database object so people can use it without looking into the code or editing code - just trying to hide it.

    Thursday, October 30, 2014 11:26 PM
  • Perhaps I'm misunderstanding something but why not use a stored procedure like you normally would for this sort of update?
    Thursday, October 30, 2014 11:39 PM
  • Hi,

    You will not be able to achive what you want using a view. You will be able to update a VIEW but updaing in a VIEW will be difficult.

    Rather use a Stored Procedure which will give you additional benefits like you will be able to pass Input parameters also.

    Friday, October 31, 2014 5:12 AM
  • Tools: MS Management Studio SQL 2008 R2
    Code:

    Create View as Mocha
    (
    UPDATE  dbo.Cola 
    SET Login_ID = Replace(PE15,RTRIM(Cast(Login_ID),'') 
    
    UPDATE dbo.cola 
    SET  P4 = NullIf(P4,'')
    
    )
    

    I would like to create a database object "View" to hand it over to a user and run the script. How I would create a a view that would accept an update statement. Are any work around to accomplish this goal

    Hi,

    1. Use SP instead of view, to do update to the tables
    2. Within SP, create the relevant view, even by dynamic Sql.

    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Kalman Toth Friday, October 31, 2014 6:46 AM
    • Marked as answer by Sandra VO Friday, October 31, 2014 4:05 PM
    Friday, October 31, 2014 5:24 AM
  • Not quite sure what you want to achieve.

    However, if your view displays rows from a single table and each row relates to exactly one row in the source table (no aggregates, no join, no transformation) then you can update it.  The update will be executed on the table it refers to.

    If you view is more complex, contains joins, aggregates and such, you can create a trigger on it to update the base table(s).

    The conditions for a view to be updatable are a little more complex then what I wrote but you can find it here under "updatable view": http://msdn.microsoft.com/en-CA/library/ms187956.aspx

    In the end, you would have a view which displays rows and can be updated.

    If all you want is to modify rows without viewing them then HuaMin Chen's solution is the one for you, create a stored procedure instead.

    Friday, October 31, 2014 3:24 PM
  • Is it possible to deploy stored procedure in MS Access 2010?
    Friday, October 31, 2014 3:38 PM
  • You're trying to do this in access?

    This is the TSQL forum. Everyone is going to assume you are asking about SQL Server here, unless you state otherwise.

    Friday, October 31, 2014 3:42 PM
  • No. I am not trying create SP in Access. Is it possible to call SP in MS access 2010? or a function? and where is my beer


    • Edited by Sandra VO Friday, October 31, 2014 3:57 PM
    Friday, October 31, 2014 3:57 PM
  • Did you execute the TSQL in SSMS? No? Then no beer.

    You're trying to alter an Access table correct?

    Just create a query in access that does the update. Your uses will be able to double click it to perform the update.

    Friday, October 31, 2014 4:20 PM