Welcome to Sign in | Help
in Search

update pe view, sql server 2005

Last post 10-24-2011, 3:54 PM by Alex. 3 replies.
Sort Posts: Previous Next
  •  10-18-2011, 2:51 PM 8971

    update pe view, sql server 2005

    Salut, cum trebuie defapt sa fie?

    drop table TEST_LIVE
    drop table TEST_HIST
    drop view VW_TEST

    create table TEST_LIVE(COL1 int, COL2 varchar(20), COL3 datetime)
    create table TEST_HIST(COL1 int, COL2 varchar(20), COL3 datetime)

    INSERT INTO TEST_HIST SELECT 1,'test 1', '2011-10-10 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 2,'test 2', '2011-10-11 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 3,'test 3', '2011-10-12 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 4,'test 4', '2011-10-13 13:04:14.403'


    INSERT INTO TEST_LIVE SELECT 5,'test 5', '2011-10-15 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 6,'test 6', '2011-10-16 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 7,'test 7', '2011-10-17 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 8,'test 8', '2011-10-18 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 9,'test 9', '2011-10-19 13:04:14.403'


    alter VIEW dbo.VW_TEST
    AS
    SELECT * FROM dbo.TEST_LIVE
    UNION
    SELECT * FROM dbo.TEST_HIST

    update a
    SET a.COL2 ='test x'
    from VW_TEST a where a.COL1 = 1

    si primesc eroarea:
    Msg 4406, Level 16, State 1, Line 1
    Update or insert of view or function 'a' failed because it contains a derived or constant field.

    nu se poate face update-ul daca am union in view?
  •  10-18-2011, 3:48 PM 8972 in reply to 8971

    Re: update pe view, sql server 2005

    Asta merge:

    create table TEST_HIST(COL1 int PRIMARY KEY CHECK (col1 BETWEEN 1 AND 4), COL2 varchar(20), COL3 datetime)
    create table TEST_LIVE(COL1 INT PRIMARY KEY CHECK (col1>4), COL2 varchar(20), COL3 datetime)

    INSERT INTO TEST_HIST SELECT 1,'test 1', '2011-10-10 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 2,'test 2', '2011-10-11 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 3,'test 3', '2011-10-12 13:04:14.403'
    INSERT INTO TEST_HIST SELECT 4,'test 4', '2011-10-13 13:04:14.403'


    INSERT INTO TEST_LIVE SELECT 5,'test 5', '2011-10-15 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 6,'test 6', '2011-10-16 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 7,'test 7', '2011-10-17 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 8,'test 8', '2011-10-18 13:04:14.403'
    INSERT INTO TEST_LIVE SELECT 9,'test 9', '2011-10-19 13:04:14.403'

    go
    CREATE VIEW dbo.VW_TEST
    AS
    SELECT * FROM dbo.TEST_LIVE
    UNION ALL
    SELECT * FROM dbo.TEST_HIST

    go
    update a
    SET a.COL2 ='test x'
    from VW_TEST a where a.COL1 = 1

    Vezi Partitioned Views in MSDN.

    Razvan
  •  10-18-2011, 3:50 PM 8973 in reply to 8972

    Re: update pe view, sql server 2005

    Vezi și secțiunea "Updatable views" din http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx.

    Răzvan
  •  10-24-2011, 3:54 PM 8978 in reply to 8972

    Re: update pe view, sql server 2005

    multumesc mult, functioneaza (eu tot oncerca cu check constraint nu si primary kwy)
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems