Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: can i update table from view?

    i have a table named X
    consists of: year,month,sales
    and a view named Y
    consists of: year,month,sales

    the view Y i got from 3 tables (A,B,X)
    year and month came from inner join from A,B,X
    and sales came from A and B

    can i update X.sales so its value will fill with the value of Y.sales?

    i've tried it with the following statement:

    Code:
    UPDATE       X
    SET                X.sales = Y.sales
    FROM            X AS X INNER JOIN
                             Y ON X.year = Y.year AND X.month = Y.month CROSS JOIN
                             X
    the view Y is:
    Code:
    SELECT        TOP (100) PERCENT X.year, X.month, N.sales, X.sales
    FROM            (SELECT        MONTH(A.date) AS month, YEAR(A.date) AS year, SUM(B.sales) AS sales
                              FROM           A AS A INNER JOIN
                                                        B AS B ON A.id = B.id
                              GROUP BY MONTH(A.date), YEAR(A.date)) AS N LEFT OUTER JOIN
                            X AS f ON N.month = X.month AND N.year = X.year
    ORDER BY X.year, X.month

    Thanks and Regards

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes you can
    Why the cross join - you don't need it

  3. #3
    Join Date
    May 2009
    Posts
    2
    i know, sql server automatically add cross join in my code. so it doesn't matter.

    but i can't run the code, it said that :
    the multipart identifier X.sales can't be bound.

    how can i get it done?

    i've also tried cursor:

    Code:
    create PROCEDURE dbo.StoredProcedure1 
    declare @qty numeric(18,0), @sales numeric(18,0),@year numeric(4,0),@mon numeric(2,0)
    declare c1 cursor for
    SELECT * from Y LEFT OUTER JOIN
              X ON Y.month = X.month AND Y.year = X.year
    ORDER BY X.year, X.month
    open c1
    fetch next from c1 into @year,@mon,@qty,@sales
    while @@FETCH_STATUS = 0
    begin
    update X set sales=@qty where year=@year and month=@mon
    fetch next from c1 into @qty,@sales
    end
    close c1
    deallocate c1
    return
    but for this cursor i still get the error on declare, but I still don't get it why, because i've seen every cursor example, and i just get the same syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •