Update a table using view with TOP clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update a table using view with TOP clause

Author

Topic

justin581209
Starting Member

2 Posts

Posted - 2010-01-14 : 00:23:50

It seems very tricky when you're looking at the Subject line. But I do have this problem here. I'm updating a column of a table, and the value needs to be update is getting from a view. But now I'm getting more than 1 values from the view, I just wanna take the value from the first row of the view. While I was told the view cannot be updated, because it contained a TOP clause. So how can I deal with this problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 01:43:09

sorry not fully clear. In beginning you told you want to update table, then why do you need to use view for that? cant you directly update on base table?

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 03:50:51

It would be better that your VIEWs don't have ORDER BY or TOP - having said that it is quite common practice.


UPDATE U
SET MyColumn = OtherColumnFromView
FROM MyTable
    JOIN
    (
        SELECT TOP 1 MyViewPK, OtherColumnFromView
        FROM MyView
        WHERE MyViewPK = 'xxxxx'
        ORDER BY MySortColumn
    ) AS X
        ON MyMainPK = MyViewPK
WHERE MyMainPK = 'yyyy'

this way the View doesn;t need to contain an Order By - indeed, you can use a Table instead of a View if you like

justin581209
Starting Member

2 Posts

Posted - 2010-01-14 : 10:05:35

Kristen, thanks so much for the advice and code! It works very well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources