Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
    select 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.

like image 534
John Livermore Avatar asked Dec 27 '22 17:12

John Livermore


2 Answers

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
like image 176
Pranay Rana Avatar answered Jan 05 '23 06:01

Pranay Rana


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

inner join A a on a.Id = b.Id
like image 41
David Amey Avatar answered Jan 05 '23 06:01

David Amey