开发者

Problem updating a sql view

I am having a problem with a sql view. My actual views encompass several joins, but for the purposes of my question I will demonstrate the issue with smaller examples.

Say I have the views…

create view A
as
    select Id as IdC
    from tableA
go

create view B
as
    select b.Id, 
            b.Name, 
            a.*
    from tableB b 
    inner join A a on a.Id = b.Id
go

So all is well. Then I change view A to read…

alter view A
as
    sel开发者_如何转开发ect Id as IdColumn
    from tableA
go

So now if I write…

select * from A

It returns column IdColumn

However, if I write…

select * from B

It returns the original IdC column name from view A

I tried sp_refreshview, but that has not helped.

How can I get view B to return the updated column name from view A?

UPDATE **

Well I messed up the original question. I thank everyone for their responses. I intend to join view A to table B in view B. It seems the alter statement on view B solves the issue.


As I can see you query you are refering tableA not view A

select b.Id, 
            b.Name, 
            a.*
    from tableB b 
    inner join tableA a on a.Id = b.Id

So modify the above query will resolve you issue

Modified query for view B

 select b.Id, 
                b.Name, 
                a.*
        from tableB b 
        inner join A a on a.IdColumn = b.Id


Your viewB is joining tableA, not view A, try:

inner join A a on a.Id = b.Id


That's the problem with select *

If you would have referenced View A

create view B 
as
     select b.Id,
              b.Name,
              a.*
     from tableB b
     inner join A  on a.Id = b.Id 

Altering View A returned columns (add, delete, rename) will not be reflected in view B until you Alter view B. Same thing happens in UDF's and Stored Procedures.


View B doesn't have anything to to with View A.

In View B you have TableA and TableB

Try instead:

alter view B
as
    select b.Id, 
            b.Name, 
            a.*
    from tableB b 
    inner join A a on a.IdColumn = b.Id
go
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜