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.
| Author |
Topic |
|
sth_Weird
Starting Member
38 Posts |
 Posted - 2010-06-29 : 05:09:05
|
| Hi,I have a problem with a very simple view.There are two tables t1 and t2. So far there were no relations between them. Recently I added a foreign key ID column in t1 which is supposed to point to an entry in t2. Both t1 and t2 contain a text column which may match, and if it does, I want to update the foreign key ID column in t1 with the corresponding ID of t2. So what I tried to do is build a view that is connected by a relation of those two text columns, and then manually update the foreign key ID.I hope you understand what I mean...Unfortunately, all columns in my view are read-only. I am not able to edit anything. I do not understand why.My query is like this (I only changed the table and column names but the rest is the same)SELECT t1.*, t2.ID FROM t1 INNER JOIN t2 ON (t1.ATextColumn = t2.AnotherTextColumn)WHERE t2.AnotherUnimportantID = 4That's all. The where-part filters the t2 table so that not all matches of AnotherTextColumn are valid matches. Thanx to the where-part the AnotherTextColumn returns unique matches.The view correctly shows the matching rows.Why is the whole view read only?What do I have to do to be able to edit?If you need any further information please ask.Thanxsth_Weird |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-29 : 05:24:38
|
Why not just execute an update statement?update t1set foreignKeyId = t2.IDFROM t1 INNER JOIN t2 ON (t1.ATextColumn = t2.AnotherTextColumn)WHERE t2.AnotherUnimportantID = 4
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2010-06-29 : 05:42:39
|
| Thank you for your answer!I tried this as well, but since not all entries in t1 have a match in t2, the query returns an error saying it can't insert NULL into the foreignKeyId. I know I could add another condition to this query but anyway I do not understand why the view is readonly.I have worked both with MS Access and the SQLServer Enterprise Manager tool (now I'm using SQL Server Management Studio, accessing the same table that I accessed with the Enterprise Manager), and I've used queries like this (and more complicated ones, too) before and it always worked fine.sth_Weird |
|
|
|
|
|
|
|
|
cannot edit simple view